iif function dont work with dates , Access 2007

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.

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?

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.

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

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

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.

please check attach  file
I use it on a query
Image4.jpg

drop the equal sign before the IIF

Gustav  now it acceptes the function but the result is   #error
aakimark the equal sign is added by access

SELECT test.[Αναγνωριστικό], test.[myfielddate], test.[description], Format(DateDiff("\d",#1/1/2015#,[myfielddate])>0,"Yes/No") AS Eκφρ1
FROM test;

Select allOpen in new window

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