Syntax for date comparison

  • Thread starter Thread starter Peter Kinsman
  • Start date Start date
P

Peter Kinsman

Private Sub cmdDisplay_Click()
If txtBRANCH < "0" Or txtPROP < "0" Or txtLAND < "0" Or txtTENT < "0"
Then
MsgBox ("All Fields Must Be Input")
Exit Sub
Else
strWhere = ""
If IsDate(txtSDATE) Then strWhere = "Date >= #" & txtSDATE & "#"
' MsgBox (DCount("Date", "Deposit Display", strWhere) & " Records")
If DCount("Date", "Deposit Display", strWhere) > 0 Then
DoCmd.OpenForm "Deposit Display", , , strWhere
DoCmd.Maximize
Else
MsgBox ("No Records Selected")
End If
End If
End Sub

The above is an event procedure which is causing great consternation - using
Access97.

The text boxes are on the selection form, and are all text except txtSDATE
which is a search date.
I am using as a test two records, in 1993 and 1995. If I set the search
date to 01/01/1995, then the message box displays "1 Records" - which is
correct - but the form still displays both records even though the filter
property of the query that the form is linked to is set to Date >=
"#01/01/1995#". Does anyone know if the syntax for the WHERE clause should
be different in the two situations please?

Many thanks

Peter Kinsman
 
If you really do have a field named Date, rename it.

That's a reserved word, used for the system date.
The expression could be true unless you set your computer clock back before
1995.
 
Thanks for the reply. I had overlooked that Date is a reserved word, but I
think the problem is that I display the records in a ListBox that is bound
directly to the query, so bypasses the Filter. I will try changing the
ControlSource of the ListBox to an SQL statement if a date is specified. I
will let you know if that works.

Peter
 
Back
Top