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