iif function dont work with dates , Access 2007

adgram1
adgram1 used Ask the Experts™
on
this is my code

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

I use  access 2007

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Yes , the data type is defined as date
Top Expert 2014

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I mean that access dont accepts the function , the regional setting is GREEK , 8/1/2016
Distinguished Expert 2017

Commented:
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.

Author

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
Most Valuable Expert 2015
Distinguished Expert 2018

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

/gustav
Distinguished Expert 2017

Commented:
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.

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

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.

Author

Commented:
please check attach  file
I use it on a query
Image4.jpg
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
I guess you need semicolons in the query designer:

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

/gustav
Top Expert 2014

Commented:
drop the equal sign before the IIF

Author

Commented:
Gustav  now it acceptes the function but the result is   #error
aakimark the equal sign is added by access
Top Expert 2014
Commented:
Please post the SQL that is generated.

Author

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

Commented:
SOME TIMES IN PROGRAMMING THE SOLUTION IS OVER OUR EYES ,
THIS IS WRONG =IIf([myfielddate]>#1/1/2015#, "yes", "no")
BUT THIS IS RIGHT =IIf([myfielddate]>#1/1/2015#; "yes"; "no")
I HATE THIS JOB

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial