Need help for date related query

[joanlhn]

Dear all, i have the following codes:

enter_date = Format(txt_date, "dd/mm/yyyy")

Set db = CurrentDb()

Set rs = db.OpenRecordset("select * from [entered_date] where Date = # " & enter_date & " # ")
If Not rs.RecordCount = 0 Then
DoCmd.Beep
Msgbox "Selected Date's report already been created", vbInformation + vbOKOnly, "Error"
Exit Sub
End If

rs.Close
Set rs = Nothing


suppose,i have one record in the table [entered_date] which matches the user input - enter_date = "08/06/2007",but whne i run the above coding,i get the rs.recordcount = 0. it suppose to be rs.recordcount = 1.
can anybody pls help? thanks

 

[neileg]

DCount() will do the same as your code. However i think it is the use of Date as a field name that is causing the problem. Date is a reserved word and should not be used as an object name.

 

[RoyVidar]

I think one additional challenge here, is how the Jet engine expects dates. I think you will need either a US date format, or another unambiguous format, for instance ISO 8601 (which is "yyyy-mm-dd")

For more info, have a look at this article by Allen Browne http://allenbrowne.com/ser-36.html

Then try this with your code
...where [Date] = # " & Format$(txt_date, "mm\/dd\/yyyy") & " # ")

 

[Dennisk]

one thing to point do not rely on the record count as it will not return a true count from a linked table. use

if not( rs.eof AND rs.bof) then