By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Query Criteria Help

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.