Days from Today calculation in query

[Guest]

Hello -

I have a field in a query

ExpDate: Format([ccExpMonth] & "/" & ([CCExpYear]+2000),"Short Date")

Where ccExpMonth and CCExpYear are integers so I had to combine two fields
into one and and convert to date format.

I want to find all records where ExpDate is less than 90 days from today.

I have tried the following in the ExpDate Criteria with no luck:

<=Now()+90
<=Date()+90

Thanks for your help!
sandy

 

[raskew via AccessMonster.com]

Hi Sandy -

What's been created with ExpDate is not a date -- it's a text monster.

Checkout this MSKB article to see how Access stores dates:
http://support.microsoft.com/kb/q130514/

You probably need to be using the dateserial function. However, as you can
see, you've failed to specify a day of the month. You'll need that. Example:


ccexpmonth = "10"
ccexpyear = "7"
y = dateserial(ccexpyear + 2000, ccexpmonth, 1)
10/1/2007
'to prove it's in datatime data format:
? cdbl(y)
39356

Today being 07-Oct-2007, Date() + 90 equals 05-Jan-08 so your <=Date()+90
equates to <= 05-Jan-08, don't think that's what you're after. Try >=Date()-
90.

HTH - Bob

Hello -

I have a field in a query

ExpDate: Format([ccExpMonth] & "/" & ([CCExpYear]+2000),"Short Date")

Where ccExpMonth and CCExpYear are integers so I had to combine two fields
into one and and convert to date format.

I want to find all records where ExpDate is less than 90 days from today.

I have tried the following in the ExpDate Criteria with no luck:

<=Now()+90
<=Date()+90

Thanks for your help!
sandy