dateadd/future date

[Donna]

I have posted a question similar to this one, but it wasn't quite working the
way I need it to. I have a calibration date (day calibration was performed)
and a calibration cycle (may be 6 m, 12 m or 24 m).
Is it possible to create a formula that will result in a future date for
calibration due?

 

[NetworkTrade]

sure; just add the number of days

FutureDate=CalibrationDate + 180

would be approx 6 mo assuming 30 day months....

 

[Michel Walsh]

Use DateAdd. The first argument indicates what kind of time unit you want to
add, such as "m" for month, "d" for days, "s" for second, "n" for
minutes (since m is already used for month). The second argument indicates
how many you want to add (or subtract, if negative), and the third argument,
the date_time to which you want to add the said amount of time:

DateAdd( "m", 6, date( ) )

produces a date 6 month from now,


DateAdd( "m", -1, date( ) )

produces a date one month in the past, from now.


DateAdd( "s", 500, now( ) )


produces a date_time 500 seconds in the future, from now ( date( ) is
today date, while now( ) is today and the actual time (hour minute
second) )



Vanderghast, Access MVP

 

[John Spencer]

Try this expression

DateAdd("m",Val(NZ([Calibration Cycle],0)),[Calibration Date])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Donna]

Thanx Michel...would you be able to get all those within a specficied time
frame?
If you want to get capture all the equipment due for the month of November
2008, whether it was due 6 m, or 12 m, would you still be able to use the
same dateadd format?

 

[Michel Walsh]

The due date would be computed, say, as:

DateAdd( "m", 6, someStartingDate)

or

DateAdd("m", 12, someStartingDate)



as a computed expression (first line of the grid, if you are using the
graphical Access interface to write your query).


To the criteria line, under the same column of the grid, it would be a
matter to add something like:

= #11/1/2008# AND < #12/1/2008#

if you prefer, that is asking that the computed expression should give a
date somewhere between (inclusively) November First 2008 and (exclusively)
December First 2008.



It is not clear to me when you use 6 and when you use12 months, but I assume
you have already handled that part (deciding if it is 6 or 12).



Vanderghast, Access MVP

 

[Donna]

I'll have to play with it to get it tweaked!
Thanx again!