Querry citeria help

[Latour]

I want to filter my query by date based on two different dates selected on a form.


However I only want to filter by date if combo box 1 has certain text in it. I have tried the criteria below in my query however it returns a query with no results. What am I missing or doing wrong?

IIf([Forms]![Form1]![Combo1]="Text1",([Table1].[Field1]) Between DateValue(Nz([Forms]![Form1]![Field2onform1],"1/1/1000")) And DateValue(Nz([Forms]![Form1]![Field2onform1],"*")))))

If I ignore the value in combo box 1 with the following criteria the formula works perfectly.

([Table1].[Field1]) Between DateValue(Nz([Forms]![Form1]![Field2onform1],"1/1/1000")) And DateValue(Nz([Forms]![Form1]![Field2onform1]

Please help with my IIF statement.....

Thanks

[aytee111]

You are referring to the first column in the combobox. Make sure that it holds the value "Text1" and not a hidden field such as ID. The first column is the first field in the row source of the combobox.

[Latour]

Thanks for the reply. I have checked this and I don't believe it to be the problem. I will give it another look.
Thanks

[aytee111]

You may have too many closing brackets.

[Micron]

What comes between the first comma and the second
IIf([Forms]![Form1]![Combo1]="Text1",
is the first value to be returned if the 1st expression is True. The next part is the value to be returned if it was False. You're trying to use a criteria expression (the part containing the AND operator) which does not return a value as the first value, and nothing for the second value. At the end, the result of the Nz function is supposed to be the second part of the BETWEEN expression, but if it's Null, it returns an asterisk. Then you'd be trying to get the DateValue of (*). You'd still have no alternate value for the IIF expression.

Not sure what all the fields represent - it all looks weird. You want to check between 2 dates from the same control? And if it is null, between 01/01/1000 and the DateValue of *? Maybe I'm not reading it right; it's been a long day.

Why not put the text control reference in one field, and the BETWEEN expression that works in the date field on the same criteria row? Still don't see how you can reference the same control for Between 2 dates:
Between DateValue(Nz([Forms]![Form1]![Field2onform1],"1/1/1000")) And DateValue(Nz([Forms]![Form1]![Field2onform1]

[Latour]

Micron,
You are right there is an error in the between statement. It should read.
Between DateValue(Nz([Forms]![Form1]![Field2onform1],"1/1/1000")) And DateValue(Nz([Forms]![Form1]![Field3onform1]