Link to home
Start Free TrialLog in
Avatar of adgram1
adgram1

asked on

iif function dont work with dates , Access 2007

this is my code

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

I use  access 2007

thanks
Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
Avatar of adgram1
adgram1

ASKER

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?
Avatar of adgram1

ASKER

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.
Avatar of adgram1

ASKER

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.
Avatar of adgram1

ASKER

please check attach  file
I use it on a query
Image4.jpg
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
drop the equal sign before the IIF
Avatar of adgram1

ASKER

Gustav  now it acceptes the function but the result is   #error
aakimark the equal sign is added by access
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of adgram1

ASKER

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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of adgram1

ASKER

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