Calculating Months 2

[mailzero28]

Hi there,

I have a TransDate,
I have a BirthDate,

I need to highlight in a query any people who have a BirthDate that is 16 months greater than TransDate

e.g going by today, their BirthDate would be earlier than 12/07/2000

I'm really stuck here, so help would be appreciated loads


bye

Martin.

 

[GKProgrammer]

Hi mailzero28,

Try using the 'DateDiff' function for example

If DateDiff("m",BirthDate,TransDate) > 16 Then
'do whatever
End iF

Regards,

GKProgrammer

 

[DougStevens]

Not exactly sure what you're asking. I believe that you want to use the DateDiff() function. You can view the Help on this function for further information.

In a gist, I believe you would use the function similiar to the following. (Writing the code in longer form to make it easier to read. Of course you could combine it down to one line of code.):

Dim nMonths AS Long
nMonths = DateDiff("m", BirthDate, TransDate)
nMonths = ABS(nMonths)

In a Query Field Expression:
NumberOfMonths: ABS(DateDiff("m", [BirthDate], [TransDate])

Then you can simply put a criteria for this field (e.g. >16).

 

[MichaelRed]

two issues to be aware of:

1)[tab]You appear to be using an international date format (12/7/2000 = 16 months implies the 12 is the date, the 7 is the month ...). Some functions in Ms. Access REQUIRE the date be in "Native" (U.S. format), while others follow the settings of hte date format on the specific machine which the function is executed on.

2)[tab]the datediff function suggested counts the 'transitions' of the interval, so it's accuracy is +/- one unit. If you use "m" then the same answer will be returned for the entire period of two months. As seen in the following (numerous) exanples:

? datediff("m", "07/12/2000", Now)
16
? datediff("m", "07/1/2000", Now)
16
? datediff("m", "07/31/2000", Now)
16
? datediff("m", "07/1/2000", #11/30/2001#)
16
? datediff("m", "07/1/2000", #11/1/2001#)
16
? datediff("m", "07/31/2000", #11/30/2001#)
16
? datediff("m", "07/31/2000", #11/1/2001#)
16


Of course, to be more 'accurate', you would need to be more specific in your 'deffinition' of month. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over