Db.OpenRecordset : with WHERE Clause Won't Find First Record 1

[CTOROCK]

I am trying to search for a date in a recordset. I currently have:


Dim Rst As Recordset
Dim Db As Database
Dim DtFcDate As Date

Set Db = CurrentDb()
DtFcDate = TxtFunctionDate.Value

Set Rst = Db.OpenRecordset("SELECT * FROM Tbl_Enquiries WHERE [Date of Function]=#" & DtFcDate & "#")

If Rst.RecordCount = 0 Then

...etc

This works fine for me except that it doesn't find the first record in the recordset. I don't know why. It a date "08/02/09" but it won't find it, but will find all others in the recordset. This is very peculiar to me. Any suggestions?

Thanks in advance for your time,

Eric






The greatest risk, is not taking one.

 

[SkipVought]

Hi,

Looking for an exact date, as yo ae doing, means that IF your date field also stores a time element, then you can miss some rows.

Rather, I would code...

WHERE [Date of Function] BETWEEN #" & DtFcDate & "#" AND #" & DtFcDate+.999 & "#")

or

WHERE [Date of Function] >= #" & DtFcDate & "#"
  AND [Date of Function] <  #" & DtFcDate+1 & "#")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

Without an ORDER BY clause there is no guaranteed order in which records will be returned and, consequently, no "First" record. Try something like

Set Rst = Db.OpenRecordset("SELECT * FROM Tbl_Enquiries WHERE [Date of Function]=#" & DtFcDate & "# ORDER BY [Date of Function]")

 

[CTOROCK]

Thank you for your responses. SkipVought, thanks, however, with the +1 it catches other dates. I short I have an unbound field in a form. when a date is entered, I would like it to open a recordset, find if there's a match. If there is it will return one of two values, depending on a value of another field on that record.

This what I have:


Dim DtFcDate As Date
Dim Rst As Recordset
Dim Db As Database
Set Db = CurrentDb()

DtFcDate = TxtFunctionDate.Value


Set Rst = Db.OpenRecordset("SELECT * FROM Tbl_Enquiries WHERE [Date of Function] >= #" & DtFcDate & "#" & " And [Date of Function] <= #" & DtFcDate & "#")

If Rst.EOF Then
Exit Sub
Else
If Rst.Fields("Deposit Amount") < 1 Then
MsgBox "This date has been enquired by" & " " & Rst.Fields("First Name") & " " & Rst.Fields("Surname")
End If
If Rst.Fields("Deposit Amount") > 0 Then
MsgBox "This date has been booked as of" & " " & Rst.Fields("Deposit Paid Date")
TxtFunctionDate.SetFocus
TxtFunctionDate.Value = ""

Exit Sub
End If

End If

Rst.Close


Thanks,


Eric

The greatest risk, is not taking one.

 

[SkipVought]

however, with the +1 it catches other dates.

The ONLY what that that could happen is if the DtFcDate contains a TIME element, in other words, DtFcDate is something like 11/13/2009 12:00, which has a Date/Time Value of 40130.5

Try this, then...

WHERE [Date of Function] >= #" & INT(DtFcDate) & "#"
  AND [Date of Function] <  #" & INT(DtFcDate)+1 & "#")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rekrabnai]

Hi, the only time i ever have problems with date comparisons is when the db seems to be comfused as to the format of it. eg what I think should be 8th February 09 (08/02/2009 - UK side of the pond) seems to be treated and 2nd August 2009 (02/08/2009).
Is there any merit in using the following line in your db?

Set Rst = Db.OpenRecordset("SELECT * FROM Tbl_Enquiries WHERE [Date of Function]=#" & Format(DtFcDate, "medium Date") & "# ORDER BY [Date of Function]")

Sorry if this is a red-herring but it often works for me.

Regards
Ian

 

[CTOROCK]

Thanks rekrabnai! The formating worked. It was an odd problem, but thanks, here's a star!

The greatest risk, is not taking one.