Date querys

[plasma]

Hello all! need some here here.

I'm making a Item db where the items have expiration dates, and what I'd like to have is a report that will show items that have expired and items that will expire in 1 month. For some reason I'm stuck getting started, could anyone point me in the right direction?

thanks..

 

[pbaldy]

Use the DateAdd function to add a month, and test for records less than that.

 

[plasma]

Use the DateAdd function to add a month, and test for records less than that.

would I do that in the Table, Query or ? and could you please give me a little more explanation on how to set this up? from what I can tell, I can specify the dateadd to look for a specific month, but how would I set it up so it so it compares the expiration date to the current date

thx

 

[pbaldy]

You could do it in a query, with a criteria on the expiration date field of:

< DateAdd("m", 1, Date())

 

[plasma]

You could do it in a query, with a criteria on the expiration date field of:

< DateAdd("m", 1, Date())

If tried this, but for some reason it's coming up with no results. I'll keeps hacking away and see if something works..

thx

 

[pbaldy]

Post a sample db if you can't figure it out.

 

[plasma]

Post a sample db if you can't figure it out.

I am unable to upload anything from work, but maybe I can explain and someone can get me started..


Tables:
Products
Just 4 columns, with an example

Product ID Product Name Lot Number Expiration Date
1 Standard1 976-3na 05/25/08
2 Standard2 522618 08/01/08



For the Report, I would like something that would show me the Products that will expire within the next month plus Products that have expired.


Again, I am sorry I cannot upload what I have (can't at work) but basically it's the same as what I posted above. I tried doing the DateAdd("m", 1, Date()) but I had no luck

thx for any help..

 

[pbaldy]

Is Expiration Date a date/time field in the table?

 

[plasma]

Is Expiration Date a date/time field in the table?

yes, it's a date/time field, although the time does not matter.

 

[pbaldy]

Then I'm stumped, because I tested that criteria on a database of mine and it worked as expected. Can you post the full SQL of the query you tried?