Query by Form (QBF) date problem

[dogman_2000]

I am trying to do a QBF and am having a problem when I try to use the
BETWEEN operator.

I have 2 text boxes on my form TxtFrom and TxtTo. The user might enter
values into both boxes or leave one of the 2 blank. Is still however
want the recorset to return some data.

At the moment I am typing the following into my query criteria field:

Between [Forms]![frmReportParameters]![TxtFrom ] And
[Forms]![frmReportParameters]![TxtTo] Or
[Forms]![frmReportParameters]![TxtFrom ]
or[Forms]![frmReportParameters]![TxtTo] is null.

This doesn't work any everytime I close the query then open it the
entire view changes.

I works if I am only working with one text box in a field and not using
BETWEEN but i have tried everything and no luck!!

Any help would be really appreciated.

Thanks in advance
Andrew

 

[David Cox]

I would try BETWEEN MIN(default_date,input_date) AND MAX(default_date2,
input_date2)
(not sure if this would work)
or

BETWEEN IIF(ISNULL(input_date1),default_date1, input_date1) AND ...

sorry, untested.

 

[John Spencer]

Add the Datefield to the query two times
Add the following as criteria

Field: DateField <<First Instance
Criteria: >= [Forms]![frmReportParameters]![TxtFrom ] OR
[Forms]![frmReportParameters]![TxtFrom ] is Null

Field: DateField <<Second Instance
Criteria: <= [Forms]![frmReportParameters]![TxtTo ] OR
[Forms]![frmReportParameters]![TxtTo ] is Null

When you close that it will rearrange the data

A second way to handle this is
Field: DateField
Criteria: Between NZ( [Forms]![frmReportParameters]![TxtTo ],#1/1/1899#) And
NZ([Forms]![frmReportParameters]![TxtTo ],#1/1/9999#)

The two methods will produce slightly different results if you leave both
input parameters blank. Method 1 will return ALL the records - including
those with no date in the field. Method 2 will return all records that have
a date between the two 1/1/1899 and 1/1/9999 - no null dates.