syntax errors when executed !!

[tuanyong]

'strSQL = "SELECT engineerID FROM " & sBrand & " WHERE " & _
' "date = '" & sDate & "' AND ((" & CInt(sStartTime) & " > CInt(freeTimeFrom) " & _
' "AND " & CInt(sStartTime) & " < CInt(freeTimeTo)) AND (" & CInt(sEndTime) & " > CInt(freeTimeFrom) " & _
' "AND " & CInt(sEndTime) & " < CInt(freeTimeTo)) OR (freeTimeFrom = '' AND freeTimeTo = ''))"

strSQL = "SELECT engineerID FROM " & sBrand & " WHERE " & _
"date = '" & sDate & "' AND ((" & sStartTime & " >= freeTimeFrom " & _
"AND " & sStartTime & " =< freeTimeTo) AND (" & sEndTime & " >= freeTimeFrom " & _
"AND " & sEndTime & " =< freeTimeTo) OR (freeTimeFrom = '' AND freeTimeTo = ''))"



response.write strSQL

rsMain.Open strSQL, adoCon

response.write rsMain("engineerID")
response.end

adoCon.close
rsblah.close

The first 2 scripts are similar but cant work either way and display some syntax errors after execution.. it goes like this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'date = '4/9/2003' AND ((0800 >= freeTimeFrom AND 0800 =< freeTimeTo) AND (1100 >= freeTimeFrom AND 1100 =< freeTimeTo) OR (freeTimeFrom = '' AND freeTimeTo = ''))'.

Could someone help ?
Thanks in advance ..

[TBor]

A couple things to look at:

(1) The very end of your SELECT statment:

OR (freeTimeFrom = '' AND freeTimeTo = ''))"

seems to be missing a couple items. You're not saying what freeTimeFrom and freeTimeTo should be equal to. Once you add those items, check over your quotation marks in the above section to make sure they're correctly placed.

(2) Not sure, but I think you might have to change your "=<" commands to "<=". Maybe not, but something to try if you still have problems after #1.

Hope this helps,
TBor

[tuanyong]

actually this statement states that if freeTimeFrom and freeTimeTo are empty, it will execute the statement.

(freeTimeFrom = "" AND freeTimeTo = ""))"


hmm.. i tried changing the <= signs but it still cant work ..
anyway thanks for your reply and help ..

[txmail]

One thing you might want to look at is the field called date, this is a reserved word in SQL and it might be causing all this grief. I had this same problem a while back and could not figure it out, I changed it to xDate and now everything works (same for time as a field name).