Phil,
What you have is correct, but it must be typed into the SQL to work. If it
is typed into the criteria in the query design grid, Access will change it
to
WHERE Date_Field = IIf(....)
The problem with this is the IIf returns True or False, the date field will
be a date, not True or False, so the equation above is always False. If you
type what you have into the SQL then look at the design grid what you'll see
Access do with it is create a calculated field
Expr1:IIf(limit_date()=true, date_field between #1/1/02# and #1/1/03#, True)
with a criteria of
<>False
I don't know why it picks <>False instead of True, but either will work.
When you go back to the SQL, what you'll see is
WHERE IIf(limit_date()=true, date_field between #1/1/02# and #1/1/03#,
True)<>False
--
Wayne Morgan
Microsoft Access MVP
"Phil" <st***@basketball.net> wrote in message
news:82*****************@newssvr22.news.prodigy.co m...
Where iif(limit_date()=true, date_field between #1/1/02# and #1/1/03#,
true)
Enterprise <va***@hotmail.com> posted in
news:e3**************************@posting.google.c om
I'm stuck. I use Access 2000. I have a date field in a query.
What I need done is if a Limit_Date function returns a true, I want
the date field to be Between 1/1/02 and 1/1/03, otherwise I want it
left alone(all records shown).
I have an if statement in the criteria:
iif(Limit_Date()=true,Between 1/1/02 and 1/1/03,"*")
This does not work since the if statement returns strings. So if
Limit_Date is true, then the criteria is "Between 1/1/02 and 1/1/03".
If its false then I get a *, which won't work with out a Like
statement.
Is there a way I can make a Between statement that accepts all dates?
I tried doing Between * and * but it does not work. I was hoping to
put if statements in the *, so if Limit_Date is true, the if would
return a date else "*".
Please help.
Thanks in advance.
--
Phil