Message If Record Not Found

[injanib via AccessMonster.com]

I have, on my form, a search button and an unbound textbox to input date
values to be searched for.
I want the code on On Click Event property of the button to filter the
database for Dates in the field called [ReceivedOn]. If there is no matching
record it should display a message saying so. I have tried the following code
and I suspect an error in the format of the dates. It will give me the error
message even if a record exists for that date. Please tell me what is wrong
with my code.

Private Sub Search_Click()
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtSearchDate) Or Me.txtSearchDate = "" Then
MsgBox "Please Enter a Date", vbOKOnly, "Data Needed!"
Me.txtSearchDate.SetFocus

Else
If DCount("*", "Tracking", "[ReceivedOn]=" & Me.txtSearchDate) > 0 Then
strWhere = "([ReceivedOn] = " & Format(Me.txtSearchDate, conJetDate)
& ")"
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No record found for the date specified. Please try again.",
vbOKOnly, "Not Found!"
Me.txtSearchDate.SetFocus
End If
End If
End Sub

 

[Douglas J. Steele]

You need to format the date for the DCount just as you are for the Filter:

Private Sub Search_Click()
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtSearchDate) Or Me.txtSearchDate = "" Then
MsgBox "Please Enter a Date", vbOKOnly, "Data Needed!"
Me.txtSearchDate.SetFocus
Else
strWhere = "[ReceivedOn] = " & Format(Me.txtSearchDate, conJetDate)
If DCount("*", "Tracking", strWhere) > 0 Then
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No record found for the date specified. Please try again.", _
vbOKOnly, "Not Found!"
Me.txtSearchDate.SetFocus
End If
End If

End Sub

 

[injanib via AccessMonster.com]

Thanks Doug, But I still encounter the same problem.
I think the problem lies here, but I am not so sure and don't know how to fix
it.
the [ReceivedOn] field's value is set to =Date() in the after update event of
the field prior to it. I have set the input mask to short date and it only
displays the date in MM/DD/YYYY format, but when I place the cursor in the
field then it displays the date and the time. Maybe the search matches the
date entered in the txtDate field with the date + time value in the
ReceivedOn field. If this is the case, why does the date field have the time
in it when the format and input mask is set to short date and the function is
Date(), not Now().

You need to format the date for the DCount just as you are for the Filter:

Private Sub Search_Click()
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtSearchDate) Or Me.txtSearchDate = "" Then
MsgBox "Please Enter a Date", vbOKOnly, "Data Needed!"
Me.txtSearchDate.SetFocus
Else
strWhere = "[ReceivedOn] = " & Format(Me.txtSearchDate, conJetDate)
If DCount("*", "Tracking", strWhere) > 0 Then
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No record found for the date specified. Please try again.", _
vbOKOnly, "Not Found!"
Me.txtSearchDate.SetFocus
End If
End If

End Sub

I have, on my form, a search button and an unbound textbox to input date
values to be searched for.

[quoted text clipped - 31 lines]

End If
End Sub

 

[Douglas J. Steele]

What time is it displaying? Dates are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. That means that if you have simply a date (so that the
decimal portion is 0), that represents midnight on that day.

Is ReceivedOn populated using the Now() function? You'll need to use

strWhere = "DateValue([ReceivedOn]) = " & _
Format(Me.txtSearchDate, conJetDate)

or (better, since it doesn't require that the DateValue function be applied
to every row in the table)

strWhere = "[ReceivedOn] BETWEEN " & _
Format(Me.txtSearchDate, conJetDate) & _
" AND " & _
Format(DateAdd("d", 1, Me.txtSearchDate), conJetDate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, But I still encounter the same problem.
I think the problem lies here, but I am not so sure and don't know how to
fix
it.
the [ReceivedOn] field's value is set to =Date() in the after update event
of
the field prior to it. I have set the input mask to short date and it only
displays the date in MM/DD/YYYY format, but when I place the cursor in the
field then it displays the date and the time. Maybe the search matches the
date entered in the txtDate field with the date + time value in the
ReceivedOn field. If this is the case, why does the date field have the
time
in it when the format and input mask is set to short date and the function
is
Date(), not Now().

You need to format the date for the DCount just as you are for the Filter:

Private Sub Search_Click()
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtSearchDate) Or Me.txtSearchDate = "" Then
MsgBox "Please Enter a Date", vbOKOnly, "Data Needed!"
Me.txtSearchDate.SetFocus
Else
strWhere = "[ReceivedOn] = " & Format(Me.txtSearchDate, conJetDate)
If DCount("*", "Tracking", strWhere) > 0 Then
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No record found for the date specified. Please try again.",
_
vbOKOnly, "Not Found!"
Me.txtSearchDate.SetFocus
End If
End If

End Sub

I have, on my form, a search button and an unbound textbox to input date
values to be searched for.

[quoted text clipped - 31 lines]

End If
End Sub

 

[injanib via AccessMonster.com]

ReceivedOn is populated using Date() function.

What time is it displaying? Dates are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. That means that if you have simply a date (so that the
decimal portion is 0), that represents midnight on that day.

Is ReceivedOn populated using the Now() function? You'll need to use

strWhere = "DateValue([ReceivedOn]) = " & _
Format(Me.txtSearchDate, conJetDate)

or (better, since it doesn't require that the DateValue function be applied
to every row in the table)

strWhere = "[ReceivedOn] BETWEEN " & _
Format(Me.txtSearchDate, conJetDate) & _
" AND " & _
Format(DateAdd("d", 1, Me.txtSearchDate), conJetDate)

Thanks Doug, But I still encounter the same problem.
I think the problem lies here, but I am not so sure and don't know how to

[quoted text clipped - 41 lines]