Query: System Date Search problem.

[Dedicated]

I have developed a dbase for auditing employees each month. Each audit will have it's own record (this means if an employee was audited 40 times for the month of June, then he should have 40 records for that month.

On my report I needed to count the # of records audited per month. I used the system date Field [SysDate]as my search field in my query. Criteria is as followed: Between [Enter Start Date]And[Enter End Date].

Problem: when searching, let's say 07/01/2004 to 07/15/2004. qry will only pull up to 07/14/2004. It doesn't pull the records for 07/15/2004. Why is this, and how can I fix it?
Thanks........

 

[PHV]

Have you tried this ?
Between [Enter Start Date] And ([Enter End Date]+#23:59:59#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dedicated]

I applied your recommendation with the following results: "This expression is too complex to be evaluated. For example, a numberic expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the ecpression to variables."

System automatically changed #23:59:59# to #11:59:59#

Your time on this issue is greatly appreciated.

Thanks,

Monique

 

[PHV]

Can you please post the WHERE Clause of your initial SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dedicated]

Sorry, this is QBE only, no SQL. However, I am game to learn if you're willing to teach.

 

[PHV]

When in the Query Design Window choose the SQL pane to see the generated code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dedicated]

WHERE ((([qry_PayEntry Error1].Datesyst) Between [Enter Start Date (System Date)] And ([Enter End Date (System Date)]+#12/30/1899 23:59:59#)));

 

[PHV]

And what about something like this ?
WHERE CDate(Format([qry_PayEntry Error1].Datesyst, "m/dd/yyyy")) Between [Enter Start Date (System Date)] And [Enter End Date (System Date)];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244