Date range from a form

[Night Owl]

Hi,

I'm trying to use a form with two unbound fields as start and end dates to
provide a date range to filter records that occur in that date period. This
seems to work well, unless the dates are blank, when I want all records to
be shown.

This is the code I'm using...

= = = = = = = = = =
Private Sub Command8_Click()

Dim strDocName As String
Dim strLinkCriteria As String
Dim datStartDate As Date
Dim datEndDate As Date

strDocName = "Form1"
datStartDate = Me.Text3.Value
datEndDate = Me.Text5.Value

If Me.Text3.Value Is Null Then GoTo AllDates

If IsDate(Text3) And IsDate(Text5) Then
If Me.Text5 < Me.Text3 Then
MsgBox "The end date must be later than the start date."
Exit Sub
End If
Else
MsgBox "Please use valid date formats."
Exit Sub
End If

strLinkCriteria = "[IncidentDate] Between #" & Format(Me![Text3], "dd
mmmm yyyy") & "# And #" & Format(Me![Text5], "dd mmmm yyyy") & "# And [Type]
= 'Certain Records'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit Sub

AllDates:

strLinkCriteria = "[Type] = 'Certain Records'"
DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub
= = = = = = = = = =

If Me.Text3.Value Is Null Then GoTo AllDates - gives an 'Invalid use of
null' error,
If Me.Text3.Value = "" Then GoTo AllDates - gives the same, as does
If Me.Text3.Value = "00:00:00" Then GoTo AllDates

Any help is appreciated.

TIA,

Pete

 

[George Nicholson]

"Is Null" is OK in SQL but not in VB. Replace
If Me.Text3.Value Is Null Then GoTo AllDates

With:
If IsNull(Me.Text3.Value) Then

HTH,