Returning Overdue Records

[JustPunkin]

I'm not even sure where to begin with this question. I am trying to come up with a query that will report on documents that have a submittal date prior to todays date.

We have a database that tracks when drawings and documents are due. I've designed a query that will allow me to return records that are due between a certain time frame and that haven't been submitted already (based on a Is Null criteria). It's a parameter query that searches "Between [Start Date] and [End Date]"

The person who will be using this database wants a query that will return late/overdue documents. I don't want to have to put in an start date, just an end date (or simply use the current date). What criteria would I use to search all records before the current date?

I hope that makes sense.

Thanks!

Brenda

 

[JustPunkin]

It's not as simple as setting the criteria for Submittal Date " < (Now) " is it?

 

[gemma-the-husky]

depends what you do to the date AFTER its submitted

so, yes it is

[submittal date] < now

or probably

[submittal date] < date

(date is just the date, now includes the time also)

but if you set a flag of some sort it might be

[submittal date] < date and [submitted flag] = false

-----------
not sure without trying it, but you may also need some # characters, depending how/where you use the expression

 

[JustPunkin]

she just wants to be able to run a report that will show what documents are late. There are no flags or anything like that. The query searches to see if the field for issue date is null (which means nothing has been sent) and if so, checks those records to see if the submittal date is prior to the current date.

I guess I can just change it to date; time isn't essential.

Thanks!

 

[gemma-the-husky]

no, but do you clear the date after the document is received - otherwise you will be showing documents as due, when they are not

just try messing with queries - see if you get the correct results

 

[JustPunkin]

no, but do you clear the date after the document is received - otherwise you will be showing documents as due, when they are not

just try messing with queries - see if you get the correct results

It appears that, after entering an issue date, the document no longer shows as due, so I think it's going to be OK.

I hate working on projects that other people have started; they never do things the right way - you know, the way I would do them

Thanks again!

 

[gemma-the-husky]

the difference between date() and now() is that the time bit in now can cause issues

if you have a date stored with a time, and you try to search for records <= a given date

i think you find that this FAILS when the date recorded is on the given date, but also includes a time


ie <= "13th March" FAILS with "13th March 13:21pm"

definitely causes potential issues.

---------
Hence, if you dont need the time (eg its useful for timestamping actions on records) then dont store the time.