Re: Makes no sence

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Completly understand but I did not want to start this dialog slamming a bunch
of code out there.
Before you look at the below information, please keep this in mind, the only
problem is the sub-report called "r_NearestDate"...
When the query is run alone, it will give the date 05/11/07 for the 4th
person but when the report is run that person will have a different date,
like the 5th nearest date. About 20% of the nearest dates are wrong on the
report but query returns the one correct date. I don't understand how the
sub-report, which was created from this query, could give a different date.

Report (r_Inventory) is created from Query (q_Inventory) and it has two
sub-reports.

q_Inventory has the following features:

1). This code counts how old the item is based on today minus receipt date:
Days: DateDiff("d",[TR_Date_TIMERCVD_HOI],Date())

2). Based on this code - it tells me when something is due:
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)))


r_Inventory is created from the above query and has two sub_reports.

Sub_report #1 comes from this query -
SELECT q_Inventory.TR_GRIEVANCECOORDINATOR, Min(q_Inventory.DueDate) AS
MinOfDueDate
FROM q_Inventory
GROUP BY q_Inventory.TR_GRIEVANCECOORDINATOR;

Sub_report #2 comes from this query -
SELECT q_InventoryCountA.TR_GRIEVANCECOORDINATOR,
Count(q_InventoryCountA.ICNNO) AS CountOfICNNO, q_InventoryCountA.TR_CLOSEDATE
FROM q_InventoryCountA
GROUP BY q_InventoryCountA.TR_GRIEVANCECOORDINATOR,
q_InventoryCountA.TR_CLOSEDATE
HAVING (((q_InventoryCountA.TR_CLOSEDATE) Is Null))
ORDER BY Count(q_InventoryCountA.ICNNO);

CONCLUSION:
When this report is run the only value screwed up is the sub_report
"r_NearestDate.


Suggestions????



"Baz" wrote:

I already suggested that, he says that isn't the problem. however, until he
gives us some examples of "correct" dates and the corresponding "incorrect"
dates, plus some more information about where the displayed dates are coming
from, there isn't really anything else to suggest.


"Tom Lake" <tlake@xxxxxxxxxxxx> wrote in message
news:eTBjO7goHHA.3460@xxxxxxxxxxxxxxxxxxxxxxx

"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE3509E6-1E16-48A3-9897-3542BF8B3C6C@xxxxxxxxxxxxxxxx
My Access db is linked to SQL db's.
I created a report then ran my mde and the users open the mde.

One date on the report returns incorrect on every PC except mine.
If I'm at a different PC open the mde the date is incorrect.
If I'm at my PC open the mde the date is correct.
If someone else sites at my PC and opens the mde the date is correct.

This makes no sense to me - The same mde is being opened.
When I'm at another PC and open the mdb the same thing, wrong date.

Makes no Sense - I don't even know what to check!!!!

Time Zone?

Tom Lake





.

Relevant Pages

  • Re: report based query
    ... I have a query and report based on that query which requires 2 parameters to ... Create an unbound form. ... When the New Form dialog opens select Design View. ...
    (microsoft.public.access.gettingstarted)
  • Re: UPDATE DATE FIELD IN QUERY
    ... In the Date Field of my Query I have entered the following into the Criteria ... I can open the query, and it does prompt start & end dates, but it opens the ... When I open the report it opens the same parameter window & not my form, ... End dates 6 times & it just kept bringing up the prompt. ...
    (microsoft.public.access.macros)
  • Re: Query data into report
    ... When this form is opened it has the two command buttons I mentioned earlier. ... When I select a name from the combobox the form main1 closes and the query is ... With this datasheet open I then open the report called ... When this report opens it has the parameter entry box ...
    (microsoft.public.access.queries)
  • Re: Calculating time worked without having an EndTime field
    ... The DMincould use a query as its domain if necessary. ... The code could go in the Open event of the report if users open the report directly from the Database Window/NavPane. ... If you provide an interface with a command button that opens the report, you could use the Click event of the command button. ... I've included below the subquery I used to get the ...
    (microsoft.public.access.queries)
  • Re: Combox box query tries to run again
    ... When the report opens it will run the query used as its RecordSource. ... If the user chooses to Cancel, the code in the Cancel button's Click event ...
    (microsoft.public.access.queries)