Date Part

[Rustin788]

Hey,

Someone sent me a query that had the following as an expression:

IIf(DatePart("m",Now())-DatePart("m",[InvDate])<0,12+DatePart("m",Now())-DatePart("m",[InvDate]),DatePart("m",Now())-DatePart("m",[InvDate]))



Criteria: <>0

I was hoping someone might be able to explain what this is doing. I want to make sure I know how to set this up myself but I've been staring at it for 20 minutes and can't work it out in my head.

Thanks.

[June7]

The expression is extracting the month component of date values using DatePart function. The same can be done with Month() function.

IIf(Month(Now())-Month([InvDate]) < 0, 12 + Month(Now()) - Month([InvDate]), Month(Now()) - Month([InvDate]))

Then the IIf() function is used to determine which expression to return a value. In plain English:

If Month(Now()) minus Month([InvDate]) is less than 0 Then 12 + Month(Now()) minus Month([InvDate]) Else just Month(Now()) minus Month([InvDate])

Alternative:

Month(Date()) - Month([InvDate]) + IIf(Month(Date())-Month([InvDate]) < 0, 12, 0)