comparing the date entered to a form and the date field in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yue Zhao - 07 Dec 2006 19:45 GMT

I have a Date/Time type field, which is called "Date", in a table called
"tblHomePrac"
Then, in a form, I enter a date (it looks like "12/1/2006" because I had
the format of the textbox to be "shortdate"), and look up the table to
see whether the date I entered is already existing in the table.

The codes are attached at the bottom. If the date is existing,
intfound1=1. But I have never got intfound1=1 even whenI am sure the
date I entered is existing.

I guess that's something about the Date/Time type and the shortdate
format - but have no idea how to convert them to be comparable.

I am wondering anybody could help me out with that.

Thanks a lot!

P.S. my codes are below:

Dim strDateCode As Date
strDateCode = Me![Date]

Dim strSQL1 As String
strSQL1 = "SELECT tblHomePrac.Date FROM tblHomePrac WHERE
(date(tblHomePrac.Date) = " & strDateCode & ")"

Dim intFound1 As Integer
intFound1 = 0

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset

Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset(strSQL1)

Do While ((Not rs1.EOF) And intFound1 = 0)

If (rs1![Date] = strDateCode) Then
intFound1 = 1
End If
rs1.MoveNext

Loop
rs1.Close

If intFound1 = 1 Then
...
Else
...
End If

Reply to this Message

Dave Miller - 07 Dec 2006 21:31 GMT

Yue,

Try this.

David Miller
**********************************************************

Sub CompareDates()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL, sDate As String
Dim i As Integer

sDate = Format(Me.Date, "MM/DD/YYYY")
sSQL = "SELECT tblHomePrac.Date FROM tblHomePrac"
Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL)

i = 0
With rst
If Not .BOF Then .MoveFirst
Do Until .EOF
If Format(!Date, "MM/DD/YYYY") = sDate Then
i = i + 1
End If
.MoveNext
Loop
.Close
End With

If i >= 1 Then
'Do Something
Else
'Do Something Else
End If
Set db = Nothing
Set rst = Nothing

End Sub

Reply to this Message

Yue Zhao - 07 Dec 2006 22:32 GMT

Thank you, Dave!

I later figured that: a pair of "#" are needed both immediately in front
of and after the date value entered, to do the comparison between the
date value from a table and that entered from the form.

And the format() as you explained is totally new to me, I will try it!

Yue

> Yue,
>
[quoted text clipped - 35 lines]
>
> End Sub

Reply to this Message