date search range in

  • Thread starter Thread starter f
  • Start date Start date
F

f

Hi,

I am creating a form in Access 2000, which allows a user
to search by multiple criteria. Once the criteria are
chosen, the user clicks on the search button, and a
results page appears.

One of the criteria is to be able to search a date range.

How do I write the criteria for this?

Thanks in advance.

-f
 
I go through the controls where the criteria can be
selected and build a string to use as a filter for the
form:

Assume we have four controls: a ComboBox(cboCriteria1), a
TextBox(txtCriteria2) and two more TextBoxes(txtstartDate
and txtEndDate) for dates:
Dim strFilter as String

If IsNull([cboCriteria1]) or [cboCriteria1]="" Then
If IsNull([txtCriteria2]) or [cboCriteria2]="" Then
If IsNull([txtStartDate]) or [txtStartDate]="" Then
' no criteria has been selected
Exit Sub
Else
strFilter = "[Date] Between #" & [txtStartDate] _
& "# And #" & [txtEndDate] & "#"
End If
Else
If IsNull([txtStartDate]) or [txtStartDate]="" Then
StrFilter = "[Field2]='" & [txtCriteria2] & "'"
Else
StrFilter = "[Field2]='" & [txtCriteria2] _
& "' AND [Date] Between #" & [txtStartDate] _
& "# And #" & [txtEndDate] & "#"
End If
End If
Else
If IsNull([txtCriteria2]) or [cboCriteria2]="" Then
If IsNull([txtStartDate]) or [txtStartDate]="" Then
strFilter = "[Field1]='" & [cboCriteria1] & "'"
Else
strFilter = "[Field1]='" & [cboCriteria1] _
& "' AND [Date] Between #" & [txtStartDate] _
& "# And #" & [txtEndDate] & "#"
End If
Else
If IsNull([txtStartDate]) or [txtStartDate]="" Then
StrFilter = "[Field1]='" & [cboCriteria1] _
& "' AND [Field2]='" & [txtCriteria2] & "'"
Else
StrFilter = "[Field1]='" & [cboCriteria1] _
& "' AND [Field2]='" & [txtCriteria2] _
& "' AND [Date] Between #" & [txtStartDate] _
& "# And #" & [txtEndDate] & "#"
End If
End If
End If

DoCmd.ApplyFilter , strFilter
Hope this helps!

Howard Brody
 
AND [Date] Between #" & [txtStartDate] "# And #" & [txtEndDate] & "#"
Click to expand...

There are a few things to be aware of in this particular solution.

The first is the internationalisation: you will get errors when you hire
your first non-US temp data clerk who uses normal d/m/y date formats.

The second is that BETWEEN only works correctly for integer values, and
DateTime expressions are not integer unless you can be certain that the
column does not contain any time values. For example 01/02/2003 12:34 lies
outside the range BETWEEN 01/02/2003 AND 01/02/2003, which you may not
expect. It's generally safer to use proper inequality expressions:

WHERE StartDate <= MyDate AND MyDate < EndDate + 1

or an explict date value cast

WHERE DateValue(MyDate) BETWEEN StartDate AND EndDate

Hope that helps


Tim F
 
Back
Top