RE: Two PCs Different Results

Tech-Archive recommends: Fix windows errors by optimizing your registry

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




"Klatuu" wrote:

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


"Dan @BCBS" wrote:

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

"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: Export Currency to a Flat File
    ... >I created the query you suggested and used the FormatNumberto ... Thanks for the good suggestion. ... >> Doug Steele, Microsoft Access MVP ... >> (no private e-mails, please) ...
    (microsoft.public.access.externaldata)
  • RE: Two PCs Different Results
    ... Dave Hargis, Microsoft Access MVP ... but the order changes on any other PC? ... This is a very simple query. ... FROM (tblTrackingData LEFT JOIN tblStatus ON tblTrackingData.TR_STATUS = ...
    (microsoft.public.access.queries)
  • Re: Records will not append to another table due to Key violations
    ... Perhaps you have tried every suggestion, but there is no way for anybody ... Can you create a query using the ... can say that if you append one table to another you will be adding new ... the records to the other table due to validation rule violations or key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Design View
    ... Someone else may be able to provide a suggestion. ... I also checked references and they seemed fine for that database, ... Okay, Bob, the query contains the reserved word DATE as a field name. ... you may be able to get the SQL statement in A2007 opening the ...
    (microsoft.public.access.queries)
  • Re: problem getting a query to recognize "Not" and "<>" in a value list combo box
    ... Source was the one to display all records, so column 1 had a value of "*", ... and column 2 had a value of "All Leases". ... After reading your first suggestion, I modified those two components of the ... This query is based on one table, ...
    (microsoft.public.access.forms)