INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

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



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...

CODE

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

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

CODE

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

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

(OP)


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.

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


Quote:

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...

CODE

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

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

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

(OP)

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

The greatest risk, is not taking one.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close