Null Value for Blank Date Range

[J. Trucking]

Hello,

I am having an issue with a query I have built. I have a query which
searches through a table and returns a couple of fields, one of which
is called DateOfWork. The query is run when a form is filled in by
the user. The user has the option to specify a date range on the form
(text boxes) or leave the dates blank. What I would like to have is,
if the dates are blank, the query returns all values. If the dates
are specified, then the query only returns values between the dates.
I have done this before, but only for one textbox/combobox. I did it
by filling the following info into the query criteria:

[Forms]![FormName]![ComboBoxName] or [Forms]![FormName]![ComboBoxName]
is null

This worked but I am unsure how to do it for two date values. I have
done the following so far:

Between [Forms]![FormName]![StartDate] and [Forms]![FormName]!
[EndDate]

It works well but I am now unsure how to incorperate the null part.
The other thing I should mention is that the date text boxes on the
form are linked to a check box on the same form (DateCheck). So if
DateCheck is unchecked, the two date text boxes are not enabled
(greyed out). If DateCheck is checked, the boxes are enabled and the
user can specify the date range that they want. Not sure if this would
assist me in the query building. Thanks in advance for any help/
advice.

John

 

[Ken Sheridan]

John:

You'll need to change the way in which the query restricts the result on the
date range:

PARAMETERS
[Forms]![FormName]![StartDate] DATETIME,
[Forms]![FormName]![EndDate] DATETIME;
SELECT *
FROM YourTable
WHERE (YourDate >= [Forms]![FormName]![StartDate]
OR [Forms]![FormName]![StartDate] IS NULL)
AND (YourDate < [Forms]![FormName]![EndDate] + 1
OR [Forms]![FormName]![EndDate] IS NULL);

The parentheses around each Boolean OR operation are essential to force them
to evaluate independently of the AND operation. Also note a couple of other
things in the above:

1. Its always a good idea to declare date/time parameters as a date entered
in short date format might otherwise be interpreted as an arithmetical
expression and give the wrong results.

2. By testing for date/time values before the day following the end date
parameter this ensures that dates on the last day of the range are returned
even if they include a non-zero time of day, which can easily creep in
unknown if steps have not been taken to prevent this in the table definition,
the most common culprit being the inappropriate use of the Now() function to
insert a date. A BETWEEN….AND operation would not return such dates as the
end of the range would be midnight at the start of the last day, there being
no such thing in Access a date without a time of day.

On your form you might, if you haven't already done so, like to include some
code to ensure that, as well as enabling/disabling the text boxes, both are
set to Null if the user unchecks the check box after entering values in the
text boxes. This can be done with code in the check box's AfterUpdate event
procedure:

' set both text boxes to Null if check box is False
If Not Me.CheckDate Then
Me.StartDate = Null
Me.EndDate = Null
End If
' disable/enable text boxes
Me.StartDate.Enabled = Me.CheckDate
Me.EndDate.Enabled = Me.CheckDate

Ken Sheridan
Stafford, England

Hello,

I am having an issue with a query I have built. I have a query which
searches through a table and returns a couple of fields, one of which
is called DateOfWork. The query is run when a form is filled in by
the user. The user has the option to specify a date range on the form
(text boxes) or leave the dates blank. What I would like to have is,
if the dates are blank, the query returns all values. If the dates
are specified, then the query only returns values between the dates.
I have done this before, but only for one textbox/combobox. I did it
by filling the following info into the query criteria:

[Forms]![FormName]![ComboBoxName] or [Forms]![FormName]![ComboBoxName]
is null

This worked but I am unsure how to do it for two date values. I have
done the following so far:

Between [Forms]![FormName]![StartDate] and [Forms]![FormName]!
[EndDate]

It works well but I am now unsure how to incorperate the null part.
The other thing I should mention is that the date text boxes on the
form are linked to a check box on the same form (DateCheck). So if
DateCheck is unchecked, the two date text boxes are not enabled
(greyed out). If DateCheck is checked, the boxes are enabled and the
user can specify the date range that they want. Not sure if this would
assist me in the query building. Thanks in advance for any help/
advice.

John

 

[J. Trucking]

Thanks Ken!

I'll give it a shot.

John