RE: Two PCs Different Results

I was only going to put it into a text box source code on the report. But
can I plug it into this query instead? But I beleive that will max out the
allowed space for code..

SELECT tblTrackingData.TR_GRIEVANCECOORDINATOR, IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0))) AS DueDate, tblTrackingData.TR_CLOSEDATE
FROM (tblTrackingData LEFT JOIN tblStatus ON tblTrackingData.TR_STATUS =
tblStatus.S_Status) LEFT JOIN tblUsers ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblUsers.RACFID
GROUP BY tblTrackingData.TR_GRIEVANCECOORDINATOR, IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0))), tblTrackingData.TR_CLOSEDATE
HAVING (((tblTrackingData.TR_CLOSEDATE) Is Null));



Thanks

"Klatuu" wrote:

Post what you need it plugged into, please
--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

It is sounding like a format issue.
I don't mean to sound stupid but could you help me plug in the code you
suggested below.

The value in my SQL is "DueDate"

Are you suggesting that a text box in my report have a control source
something like:
If DueDate(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function



"Klatuu" wrote:

That is the only thing I can think of. Even if IT controls them, you should
be able to view them. If you can't, check with them.

I copied this from Allen Browne's web site. It might help:

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. allen@xxxxxxxxxxxxxxx, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

Our IT department controls all the regional setting through out Blue Cross FL.
The code in the query that returns the "DueDate" give me the correct answer
but the order changes on any other PC?

I'm trying everything I can think of, sorting, creating new queries, doing
the same on other PC's.. No matter what I do the order only works on this
PC...

That makes no sense.........



"Klatuu" wrote:

Dan,

I don't know the answer for sure, but here is something you can check, if
you have not already.

Be sure the regional settings on both computers are the same.

--
Dave Hargis, Microsoft Access MVP


"Dan @BCBS" wrote:

Sort changes between PC's!!
With Access 2000 and tables linked to SQL db tables.
I get different results on any other PC and it makes no sense.

This is a very simple query. (Read notes below)

SELECT tblTrackingData.TR_GRIEVANCECOORDINATOR, tblTrackingData.TR_CLOSEDATE
FROM (tblTrackingData LEFT JOIN tblStatus ON tblTrackingData.TR_STATUS =
tblStatus.S_Status) LEFT JOIN tblUsers ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblUsers.RACFID
GROUP BY tblTrackingData.TR_GRIEVANCECOORDINATOR, tblTrackingData.TR_CLOSEDATE
HAVING (((tblTrackingData.TR_CLOSEDATE) Is Null));

Then the following code added gives me a due date. I get the correct
information on my PC. When I run this same query on another PC the Due Date
order changes.
Does not matter if I sort by due date, I have also created a new query from
this query and sorted by Due Date - when I go to any other PC the Due Date
order changes. This is messing me up because I need to identify the nearest
date in a report.

DueDate: IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#07/01/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0)))

PLEASE HELP
.

Relevant Pages

  • RE: Transpose data in a report-Field values broken down to separat
    ... Microsoft Access MVP ... "Duane Hookom" wrote: ... Then create a main report based on only the unique values of ID and Name. ... a query, along with my response, that explains the need further: ...
    (microsoft.public.access.reports)
  • RE: Sorting in Query and Report involving look up combo box
    ... Dave Hargis, Microsoft Access MVP ... query and the report and the best way to do that is having the look up field. ... I have the report grouped by Artist Name and then Sorted by Style Number ...
    (microsoft.public.access.reports)
  • RE: Sorting in Query and Report involving look up combo box
    ... Dave Hargis, Microsoft Access MVP ... "Esaw" wrote: ... I have the report grouped by Artist Name and then Sorted by Style Number ... the output of the query based on the field or fields in the ORDER BY ...
    (microsoft.public.access.reports)
  • RE: Mutliply reports all at once
    ... button to run the report. ... Dave Hargis, Microsoft Access MVP ... "The Uke" wrote: ... Now when you run the query, you will see a column with the name PrintReport ...
    (microsoft.public.access.reports)
  • RE: Sorting in Query and Report involving look up combo box
    ... Dave Hargis, Microsoft Access MVP ... "Esaw" wrote: ... query and the report and the best way to do that is having the look up field. ... I have the report grouped by Artist Name and then Sorted by Style Number ...
    (microsoft.public.access.reports)