IIF statement using dates

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

Hello Mark,

Dates always need to be enclosed in #s. Without the #s Access thinks your
date expression is division. Try this:
IIf([Completion Date]#1/1/2008#,"YES","NO")

Steve




wrote in message
...
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

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.

Hope that helps

wrote:
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

--
a href="
http://www.software-matters.co.uk/bespoke-database-design.html"Bespoke
Access Database Development/a
pSoftware Matters/br
Straightforward solutions that work/p

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1

On Thu, 26 Feb 2009 16:35:47 GMT, "Software-Matters via AccessMonster.com"
u49864@uwe wrote:

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")
--

John W. Vinson [MVP]

"John W. Vinson" wrote in message
...
On Thu, 26 Feb 2009 16:35:47 GMT, "Software-Matters via AccessMonster.com"
u49864@uwe wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)