Problem comparing date in form to date in table 1

[Prattdakota]

I'm using the BeforeUpdate event from a form to call a subroutine which will pull up a related record in a table. I use the code that follows to attempt to find the correct record in the table. Ignore the fact that the date field is called txtScheduleDate, I've checked and both fields I'm trying to compare (the one in the form and the one in the table) are date fields. Using the ">" in my WHERE Clause, I've actually brought in a record which has a date of 1/9/03 and the form date field has a date of 1/10/03 which is actually greater. It will still find the record if the form field's value is 12/31/99. I don't know why it isn't comparing correctly. Any ideas?

Dim rstAvailable As ADODB.Recordset

Set rstAvailable = New ADODB.Recordset

With rstAvailable
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "SELECT * FROM tblAnalystAvail " & _
"WHERE tblAnalystAvail.ADate >" & _
Forms!frmschedule!txtScheduleDate
End With

If rstAvailable!adate = Forms!frmschedule!txtScheduleDate Then
MsgBox ("yes&quot
Else
MsgBox ("no&quot
End If

 

[BillPower]

Just an idea, you cold try:

.Open "SELECT * FROM tblAnalystAvail " & _
"WHERE tblAnalystAvail.ADate >#" & _
Forms!frmschedule!txtScheduleDate & "#"

 

[Prattdakota]

That did the trick. Thanks.