I have a problem with an access query.  I have a work order program and I need to develope a query that will give me any work orders over thirty days old and not complete.  Two fields I am using search criteris on are Date (date work order was submitted) and JOB COMPLETE (Yes or No).  In the Date field criteria I am using:

>=Date()-30

and in the JOB COMPLETE criteria I am using:

"NO".  

The query works, however it  not only gives me "30 days past due", it also gives me Work Orders submitted two days ago.  I only want work Orders that are 30 days Past due.  I know its simple, just figure it out.

Any suggestions.



____________________________
C.......................

Hi
The function to use is "DateDiff" this gives you date comparisons in days/years/months/minutes/etc.s
"d" is days, so:

Use DateDiff("d",table.field,now)>30
er..
it might be
DateDiff("d",now,table.fieldname)>30

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Kieron,

Thanks for help.  Played with it a bit and here is how I got it to work.  This was in the expression:

DateDiff("d",[WORK ORDER REQUEST].[DATE],Now())

and in the criteria field:

>=30.  

This will give only the ones that are 30 days past due.  If you use just >30, you will receive everything that is 30 days or more past due.

You put me on the right track and made me think.

            

____________________________
C.......................