Trouble with date comparison in Access 2007

I'm have a bit of trouble with some date comparison code in Access 2007 & VB 6.5. I have a date/time field in a table formatted as dd/mm/yyyy hh:nn:ss. User input is through a form containing a field with the same formatting. I need the form to display a record based on the date/time input on the form.  When i run the code below, the database returns null.

I have even tried copying & pasting the data straight out of the database and replacing the cboQuoteDate variable with it, to no avail. I am aware of the way Access stores dates, but I'm not sure of the functions or methods required to convert them to a usable format. What I am sure of though is that this *is* simply a formatting issue (i hope!).

quote_id & quote_date are is a fields in table Quotation, and cboQuoteDate is a text field on my form. I'm a self-taught newbie when it comes to VB, so please bear with me!
Private Sub btnGetQuote_Click()
'get the quote number
Dim SQL As String
Dim rs As ADODB.Recordset
Dim quoteNo As Variant
 
SQL = "SELECT quote_id FROM QUOTATION WHERE quote_date = #" & cboQuoteDate.Value & "#"
 
Set rs = New ADODB.Recordset
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
 
quoteNo = rs.GetRows
rs.Close
 
End Sub

Open in new window

mistrynAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
The way to handle this is, for SQL, always to format to the "reverse" US standard:

strDateTime = Format(cboQuoteDate.Value, "mm\/dd\/yyyy hh\:nn\:ss AM/PM")
SQL = "SELECT quote_id FROM QUOTATION WHERE quote_date = #" & strDateTime & "#"

Never concatenate a string and a date value directly.

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Maybe:

SQL = "SELECT quote_id FROM QUOTATION WHERE quote_date =CDate( #" & cboQuoteDate.Value & "#")

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
or

SQL = "SELECT quote_id FROM QUOTATION WHERE quote_date = #" & CDate(cboQuoteDate.Value) & "#"

or


SQL = "SELECT quote_id FROM QUOTATION WHERE quote_date = # " & DateValue(cboQuoteDate.Value) & "#"

mx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... ignore my very first post ...

mx
0
 
mistrynAuthor Commented:
Thanks very much databaseMX!  The cdate function seems to do what I want it to, and perhaps a little more.  It's swapping the day and month fields, so 04/12/2007 17:27:37 becomes 12/04/2007 17:27:37. I've had this problem before, and had to rig the VB code to swap the month/day. I'm sure there's a more elegant way to do this...any advice?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"any advice?"

Pretty much what gustav just said ....

mx
0
 
mistrynAuthor Commented:
Thanks again gustav. The format function worked perfectly!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.