Criteria date query

Lensmeister

Registered User.
Local time
Today, 20:18
Joined
Feb 18, 2009
Messages
65
Hi,

I want to query a table in Access 2003.

I have set up the query and under criteria I need to to just select the items that are due for review. the items that need to be reviewed need to be done 21 days prior to the expiry date.

i.e.
Item 1 expiry date = 31/07/2015. The query would show this up on 10/07/2015 and all those items that need reviewing as well.
Item 2 expiry date = 10/06/2015 would also show up.

I read http://www.access-programmers.co.uk/forums/showthread.php?t=132555

and I think that in the review date criteria I need to use some sort of calculation like in that thread above.

Can anyone advise please?

EDIT:
I came across this;
Code:
SYNTAX - DateAdd(interval, number, date)
therefore I think if I use this
Code:
expr1: DateAdd('d', -21, [fieldname])
it should work.

Am I right?
 
Last edited:
Item 2 suggests that you want to continue showing the items therefore in the review date criteria put

<Dateadd('d',22,Date())

If you only wish to show items on the day then

=Dateadd('d',21,Date())

Brian
 
Superb Brian thanks :)

Just as an aside.

If I want another column to show up the word expired in it if it is prior to "today" how could I do it?

Would I use the same field again, with an if statement in the criteria?

I tried to do it like this with

Code:
Iif( <date(),"Expired","")

But again it did not work :(
 
You are missing the field name:
Code:
IIf[COLOR="DarkRed"]([YourField][/COLOR]<Date(),"Expired","")
 
Create a new column in the query

Then something like this

Expired:IIF(yourdatefield<Date(),"yes","no")

or whatever names you wish

Status: IIf(yourfield<Date(),"expired","")

Brian

Ah Minty beat me to it.
 
Thanks Brian AND Minty :)

See it had to be something simple and now saves me :banghead:
 

Users who are viewing this thread

Back
Top Bottom