working with dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a query that shows expiry dates due in certain ranges
i.e. 60 days, 30 days, etc. I've tried the DateAdd and the DateDiff function
and they don't work. Any suggestions?
 
Can you tell us exactly what you want to do?

Do you have a date that you enter in a form? Would you like to then
calculate an expiration date by adding a set number of days to the entered
date? Is the original date stored in a table?

We need some details please.
 
I have a database with a bunch of expiry dates in it. In the end, I want to
generate a report that tells me which certificates are coming due within 60
days of the current date.
 
Lonita,
I don't have Access on my desktop, so I am going from memory. You might want
to try something like this.

Let's say your expiration date field is called fldExpiration

Place a text box on your report and write in the expression builder:
IIf(fldExpiration - Now() = 60,"Expires in 60 days.","")

This will allow you to take the expiration date and subtract the current
date using Access' built in date function.

You could use a select case in VBA or you could nest this if statement to
obtain other values.

Chris
 
I would create a query (and base a report on it). In the query, under the
expiry dates, put criteria like...

<Date()+60

That will pull all records where the expiry is less Today plus 60.

Keep in mind it will pull records that expired in the past as well. If an
expiration was a month ago, would you change the record to the new date, or
add a new record? You may need to use a "between" criteria to prevent the
old stuff from showing up.
 
Sorry Mr. Rick B,
May l know how to create between and may l change
<Date( ) + 30
to
< Date ( ) + 1 month
or
< Date ( ) + 1 year

Thanks in advance
 
Use the DateAdd function to do that

DateAdd("yyyy",1,Date()) Adds one year to the current system date

DateAdd("m",1, Date()) Adds one month to the current system date

The following as critiera would find all records where the Datefield had a
value between today's date and one month from today's date.
Between Date() and DateAdd("m",1,Date())
 
Back
Top