By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

recordset incorretcly resulting in (EOF and BOF) = True

100+
P: 171
Hi,
I have got this code to interogate a time card table, it selects all records where a the currently signed in user has logged his time in on todays date, and not logged his time out.

The issue is that even when I the User has logged in and not logged out on the day, the result of the recordset is still rst.EOF = True and rst.BOF = True, which indicates the recordset is empty. when I run this query in the query builder it comes up with a record (I.E. rst.EOF = False and rst.BOF = False).

Expand|Select|Wrap|Line Numbers
  1. Dim strSQLEndNull  as String
  2. strSQLEndNull = "SELECT Date, UserID, TimeEnd " & _
  3. "FROM tblTimeCard " & _
  4. "WHERE Date= #" & Date & "# AND UserID = '" & CurrentUser & "' " & _
  5. "AND TimeEnd Is Null AND TimeIn Is Not Null"
  6. Set cnn = CurrentProject.Connection
  7. rst.Open strSQLEndNull, cnn, adOpenDynamic, adLockOptimistic
Thanking You
Oct 1 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
I think your problem is that you did not include TimeIn on the selection list. Try including it and tell me what happens.
Oct 5 '08 #2

ADezii
Expert 5K+
P: 8,669
In addition to what puppydogbuddy has said, since [TimeIn] and [TimeEnd] are Date/Time values, you may also need the '#' Delimiter for these Fields:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQLEndNull  As String
  2. strSQLEndNull = "SELECT Date, UserID, TimeEnd " & _
  3. "FROM tblTimeCard " & _
  4. "WHERE Date= #" & Date & "# AND UserID = '" & CurrentUser & "' " & _
  5. "AND #" & TimeEnd & "# Is Null AND #" & TimeIn & "# Is Not Null"
  6.  
  7. Set cnn = CurrentProject.Connection
  8. rst.Open strSQLEndNull, cnn, adOpenDynamic, adLockOptimistic
Oct 5 '08 #3

100+
P: 171
Thanks Guys I have just used the query builder to build this query, but I will definatley keep that in mind for next time.

Much Appreciated
Oct 8 '08 #4

Expert 100+
P: 635
Hi

My only other thought is, if you are in the UK (or not usung US dates then perhaps you need this in the code version

WHERE Date= #" & format(Date,”mm/dd/yy”) & "# AND ……

Just a thought as this does not apply to queries in the query designer and continually trips me up (I never learn)!

??

MTB
Oct 8 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.