Two PCs Different Results

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.........

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

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

 

[Guest]

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. (e-mail address removed), 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

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.........

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

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

 

[Guest]

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

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. (e-mail address removed), 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

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.........

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


:

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

 

[Guest]

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

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

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. (e-mail address removed), 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

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.........



:

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


:

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

 

[Guest]

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

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

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

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. (e-mail address removed), 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


:

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.........



:

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


:

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

 

[Guest]

I think this is correct, but I can't test it here.
See In Line below
--
Dave Hargis, Microsoft Access MVP

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, Format(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))), "\#mm\/dd\/yyyy\#") 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

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

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



:

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. (e-mail address removed), 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


:

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.........



:

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


:

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

 

[Guest]

As I feared - that exceeds the 1,024 char limit.
Any Suggestions?
I'll try creating a new query from the orginal the adding your suggestion to
the new one...

I think this is correct, but I can't test it here.
See In Line below
--
Dave Hargis, Microsoft Access MVP

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, Format(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))), "\#mm\/dd\/yyyy\#") 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

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


:

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



:

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. (e-mail address removed), 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


:

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.........



:

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


:

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

 

[Guest]

Sorry, don't have any suggestions for you on this.
--
Dave Hargis, Microsoft Access MVP

As I feared - that exceeds the 1,024 char limit.
Any Suggestions?
I'll try creating a new query from the orginal the adding your suggestion to
the new one...

I think this is correct, but I can't test it here.
See In Line below
--
Dave Hargis, Microsoft Access MVP

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, Format(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))), "\#mm\/dd\/yyyy\#") 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

:

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


:

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



:

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. (e-mail address removed), 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


:

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.........



:

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


:

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

 

[Guest]

Thanks
I'm rewritting the code with your format suggestion...

Appreciate the help..

Sorry, don't have any suggestions for you on this.
--
Dave Hargis, Microsoft Access MVP

As I feared - that exceeds the 1,024 char limit.
Any Suggestions?
I'll try creating a new query from the orginal the adding your suggestion to
the new one...

I think this is correct, but I can't test it here.
See In Line below
--
Dave Hargis, Microsoft Access MVP


:

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, Format(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))), "\#mm\/dd\/yyyy\#") 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

:

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


:

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



:

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. (e-mail address removed), 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


:

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.........



:

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


:

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

 

[David W. Fenton]

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..

This is all complex enough my eyes spin, and I haven't gone back
through the thread, but have you considered using DateSerial()? It's
what's usually used to insure correct results in SQL that has to run
with different regional settings for dates.