IIF statement using dates

[markbeeken]

Can anyone help please?

I am using the following formula to try and tell me if an entry is
greater than a certain date:

IIf([Completion Date]>1/1/2008,"YES","NO")

However, regardless of the "Completion Date", the formula always
returns "YES". Is this because it isn't recognising it as a date?

Any help would be much appreciated!

Mark

 

[John W. Vinson]

Try this:

iif(DateDiff ('d', #01/01/2008#,[Completion Date]) > 1,"YES","NO")

Actually just had another thought. Check your date format i.e. dd/mm/yyyy or
mm/dd/yyyy etc this can sometimes have a funny effect on dates.

to amplify... if you use a literal date such as #3/2/2009# Access will always
interpret it in American form, month-day-year (March 2). This is the case even
if your regional settings are in the (admittedly more logical) day-month-year
form.

Mark, if your intent is to compare the completion date to the first day of the
current year (whenever the query is run), you should use the current year
explicitly rather than a literal date:

Iif([Completion Date] > DateSerial(Year(Date()), 1, 1), "this year", "earlier
year")

 

[Douglas J. Steele]

Try this:

iif(DateDiff ('d', #01/01/2008#,[Completion Date]) > 1,"YES","NO")

Actually just had another thought. Check your date format i.e. dd/mm/yyyy
or
mm/dd/yyyy etc this can sometimes have a funny effect on dates.

to amplify... if you use a literal date such as #3/2/2009# Access will
always
interpret it in American form, month-day-year (March 2). This is the case
even
if your regional settings are in the (admittedly more logical)
day-month-year
form.

<picky>
Except for days of 13 or higher.

In other words, #12/01/2009# will ALWAYS be interpretted as 01 Dec, 2009,
whereas as #13/01/2009# will be interpretted as 13 Jan, 2009.
</picky>