Date Difference Issue

[owenpeck]

I'm having a problem trying to get an Access query to agree back to an old excel formula.

The excel formula was round(datedif(g17,g18,"m")+(datedif(g17,g18,"md")+1)/(day(date(year(G18),month(G18)+1,0))),0)

If got the query calculating the number of months but depending on the number of days into the month a date is I sometimes return a value that is wrong by 1 month. I'm also trying to nestle this calculation into a series of IIF statements as below.

Insured months 64 to 71: IIf([insured exposure ends]<#01/01/1964#,0,(IIf([insured exposure ends]>#31/12/1971# And [insured exposure starts]<#01/01/1964#,(DateDiff("m",#01/01/1964#,#31/12/1971#)+1),IIf([insured exposure ends]>#31/12/1971# And [insured exposure starts]>=#01/01/1964#,(DateDiff("m",[insured exposure starts],#31/12/1971#)+1),(IIf([insured exposure starts]<#01/01/1964#,(DateDiff("m",#01/01/1964#,[insured exposure ends])+1),IIf([insured exposure starts]>#01/01/1964#,(DateDiff("m",[insured exposure starts],[insured exposure ends])+1))))))))

If anyone can tell me how do properly replicate the forumla I would be most grateful

 

[HiTechCoach]

I have found the when using data as criteria like this: #31/12/1971# they must be in the mm/dd/yyy format. Try changing the #31/12/1971# to #12/31/1971#

See:
International Dates in Access


This may also help:

Age: DateDiff("yyyy",[DOB],Date()) does not always work correctly

There is an issue with DateDiff working properly usnless you do it like this:

Age: DateDiff("yyyy",[DOB],Date())+(DateSerial(Year(Date()), Month([DOB]), Day([DOB])) >= Date())