Calculate # of months

[Zonie32]

Using WINXP, Access 2003

How would I write a query formula to show my date Field which is called RG_Exp_Date minus (-) a date of 8/1/2003 to get the # of months between the 2 dates?

I tried #Months[RG_Exp_Date]-8/1/2003), but it gives me #ERROR# in the query field. Any help? Thanks a lot.

 

[DotNetGnat]

use DateDiff() function

-DNG

 

[DotNetGnat]

something like:

DateDiff("m",RG_Exp_Date,8/1/2003)

-DNG

 

[PHV]

DateDiff('m',#2003-01-01#,[RG_Exp_Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Sorry for the typo:
DateDiff('m',#2003-08-01#,[RG_Exp_Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zonie32]

Thanks a lot all. I forgot about that DateDiff function. PHV, your solution worked out great.

 

[DotNetGnat]

i always get the syntax wrong in Access

-DNG

 

[Zonie32]

Is there a way to add to this formula to show if [State]=FL, then subtract 11/1/2003 from RGExpDate and if it is any other [State] to subtract 8/1/2003?

 

[DotNetGnat]

once again.., not sure of syntax..but using IIF() you can do it

-DNG

 

[DotNetGnat]

i will try...may be PHV will correct me

IIF([state]='FL',DateDiff('m',#2003-11-01#,[RG_Exp_Date]),DateDiff('m',#2003-08-01#,[RG_Exp_Date]) )

is that right PHV??

-DNG

 

[PHV]

DateDiff('m', IIf([State]='FL',#2003-11-01#,#2003-08-01#), [RG_Exp_Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

Cool. Simple answer from the expert. Thanks

-DNG

 

[MichaelRed]

but you always need to be careful using datediff. it returns "interval boundaries", not an arthmatic value. e.g;

? datediff("y", #12/31/04#, #1/1/05#)
 1 
? datediff("m", #12/31/04#, #1/1/05#)
 1

or, be careful what you ask for, you may get it - even though you wern't careful in hte asking



MichaelRed