iif function dont work with dates , Access 2007

this is my code

=IIf([myfielddate]>#1/1/2015#, "yes", "no")

I use  access 2007

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you sure mydatefield is actually defined as date?  It may look like a date to a human but if the data type isn't a date data type then Access will do a string compare.
adgram1Author Commented:
Yes , the data type is defined as date
Is the source table resident within your MS Access database or is this an attached table?
What is your regional setting for datetime formats?
When you write "don't work", exactly what do you mean?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

adgram1Author Commented:
I mean that access dont accepts the function , the regional setting is GREEK , 8/1/2016
adgram, It is still not clear exactly what problem you are experiencing.  The IIF() should work as written unless there is some subtle syntax error that we are missing.  Are you getting an error message or are you getting invalid results?

Dates are tricky in that in some instances you must use the mm/dd/yyyy format whenever the interpretation can be ambigous.  Is 1/2/16 Jan 2nd or Feb 1st?  In Greece, do you use the dd/mm/yyyy format for dates?  If so, whenever you create a string expression (which #1/1/2016# is), you need to use the mm/dd/yyyy format.  Of course to avoid confusion for yourself, you could use yyyy/mm/dd instead.
adgram1Author Commented:
I will try to exlain , when I write      =[myfielddate]       it is ok
when I write     =IIf([myfielddate]>#2015/01/01#, "yes", "no") or  1/1/2015 or 01/01/2015 it says not valid syntax
Gustav BrockCIOCommented:
You can use DateDiff and Format:

    =Format(DateDiff("d",#1/1/2015#,[myfielddate])>0, "Yes/No")

But is sounds like your field is not date, thus:

    =Format(DateDiff("d",#1/1/2015#,DateValue([myfielddate]))>0, "Yes/No")

I had a test table with a date field in it that is inappropriately named "Month" so don't get hung up on that.  This expression worked for me.


I copied and pasted yours and just changed the name of the date field.

Where are you using this expression?  I assumed a control on a form or report.
adgram1Author Commented:
please check attach  file
I use it on a query
Gustav BrockCIOCommented:
I guess you need semicolons in the query designer:


drop the equal sign before the IIF
adgram1Author Commented:
Gustav  now it acceptes the function but the result is   #error
aakimark the equal sign is added by access
Please post the SQL that is generated.
adgram1Author Commented:
SELECT test.[Αναγνωριστικό], test.[myfielddate], test.[description], Format(DateDiff("\d",#1/1/2015#,[myfielddate])>0,"Yes/No") AS Eκφρ1
FROM test;

Open in new window

adgram1Author Commented:
THIS IS WRONG =IIf([myfielddate]>#1/1/2015#, "yes", "no")
BUT THIS IS RIGHT =IIf([myfielddate]>#1/1/2015#; "yes"; "no")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adgram1Author Commented:
I think that the problem is in regional setings, Gustav Brock gave me the idea forsemicolons  and also aikimark gave me the idea to find the solution working with sql code. Thanks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.