Quering from a Form (Access 2003)

[rpboll]

I have a query that has a date field. I also have a form that has two
text boxes to enter a date range. In the criteria section of the query
when I enter the following it returns the expected number of rows:

=DateValue([forms]![tbl_log]![fromdate]) And <=DateValue([forms]![tbl_log]![thrudate])

However, when I enter the following, (based on whether a radio button
is true), it does not return anything:

IIf([forms]![tbl_log]![optionAll].[value]=-1,>=DateValue([forms]![tbl_log]![fromdate])
And <=DateValue([forms]![tbl_log]![thrudate]),[DateField])

This says that if the radio button is true, then perform the query
(that works) above, otherwise display everything in the Datefield.

Any suggestions appreciated!

RBollinger

 

[kingston via AccessMonster.com]

You need to force it to evaluate the string first then use it as a criteria
by placing the IIF() inside the term(s):

BETWEEN
"#" & IIF([forms]![tbl_log]![optionAll].[value]=-1,DateValue([forms]![tbl_log]
![fromdate],[DateField]) & "#"
AND
"#" & IIF([forms]![tbl_log]![optionAll].[value]=-1,DateValue([forms]![tbl_log]
![thrudate],[DateField]) & "#"

Check my formatting.

I have a query that has a date field. I also have a form that has two
text boxes to enter a date range. In the criteria section of the query
when I enter the following it returns the expected number of rows:

=DateValue([forms]![tbl_log]![fromdate]) And <=DateValue([forms]![tbl_log]![thrudate])

However, when I enter the following, (based on whether a radio button
is true), it does not return anything:

IIf([forms]![tbl_log]![optionAll].[value]=-1,>=DateValue([forms]![tbl_log]![fromdate])
And <=DateValue([forms]![tbl_log]![thrudate]),[DateField])

This says that if the radio button is true, then perform the query
(that works) above, otherwise display everything in the Datefield.

Any suggestions appreciated!

RBollinger