how to get this sql query to work

[associates]

Hi,

I got a quick question here. I've been trying to get this to work. The problem is i don't get any syntax error so that means it's alright but i don't get anything out in the listbox (empty in the listbox when there should be records in the listbox).

Here is my sql query looks like

strSQL = "SELECT DISTINCTROW [Time Sheet].TimeSheet_ID, Staff_register.First_Name, Staff_register.Last_Name, [Time Sheet].WorkDate, [Time Sheet].Industry_No, [Time Sheet].Client_No, [Time Sheet].Job_No, [Time Sheet].SubJob_No, [Time Sheet].Schedule_ID "
strSQL = strSQL & "FROM [Time Sheet] INNER JOIN Staff_register ON [Time Sheet].Staff_ID = Staff_register.Staff_ID "
'strSQL = strSQL & "WHERE " & col & " like '" & myval & "'"
strSQL = strSQL & "WHERE [Time Sheet].TimeSheet_ID like '" & Me.CB_TimesheetID & _
"' AND Staff_register.First_Name like '*' AND (([Time Sheet].WorkDate)" & _
" between '1/1/2003' AND '1/10/2005')"

Thank you for your help in advance and look forward to hearing from you

 

[PHV]

You may replace this:
" between '1/1/2003' AND '1/10/2005')"
with this:
" BETWEEN #2003-01-01# AND #2005-10-01#)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[associates]

Thank you PHV for your reply.

It works now. However, i don't understand why it doesn't like "1/1/2003" but happy with "2003-01-01" when the date records stored in the table are in the format of '1/1/2003'

Oh, This is a pain because i have to write a function to convert it into "year-month-day" when reading from the table.

Is there a builtin function that does this conversion?

Thank you very much

 

[PHV]

Format(varDate, "yyyy-mm-dd")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886