Multiple "BETWEEN...AND" statements

[Emily T]

I have 3 different fields using a "Between...And" criteria; if i only use one
it works fine; but the query results ignore the criteria if more than one
field has the "Between...And".

Any ideas what could be the problem? Are there any prerequisites that need
to be in place before I use these three criteria?

I am using the following:

Between [Forms]![Data Form]![USD Low] And [Forms]![Data Form]![USD High]
Between [Forms]![Data Form]![Date early] And [Forms]![Data Form]![Date late]
Between [Forms]![Data Form]![XDate early] And [Forms]![Data Form]![XDate late]

And as a second step I want to be able to leave the text boxes on Data Form
blank in case the user wants all records. I used:

or [Forms]![Data Form]![USD Low] IS NULL
or [Forms]![Data Form]![USD High] IS NULL

but that also does not work. Any recommendations for a fix??

Thanks.

 

[KARL DEWEY]

Try putting the criteria on a differnt row for each.

All on the same row means that all criteria must be met to pull records.

 

[Michel Walsh]

I would use the SQL view, just to be sure. Assuming the three conditions are
to be applied to 3 different date_time fields: f1, f2 and df3:


..... WHERE ( f1 Between Nz([Forms]![Data Form]![USD Low], #1/1/1900#) And
[Forms]![Data Form]![USD High] )
AND ( f2 Between Nz([Forms]![Data Form]![Date early], #1/1/1900#) And
[Forms]![Data Form]![Date late] )
AND (f3 Between Nz([Forms]![Data Form]![XDate early], #1/1/1900#) And
[Forms]![Data Form]![XDate late] )



Note that this won't keep records where f1 (or f2, or f3) are null, though.


The idea is to use a very early date, if the form control does not supply
any. Here, I used January first of 1900. It should execute faster than
using the OR formulation (but again, the two formulations are NOT logically
equivalent, mainly in the case where f1, f2 or f3 are null).


Vanderghast, Access MVP