Creating a SQL Query

Question

I'm having trouble creating a query.

I need to create a SQL query where ProductID = prodSelect (user choice) and EndTime = Null and StartTime < 3 hours from the current time.

This is what I have so far. Could someone help me figure it out?

Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = prodSelect 
       AND EndTime = NULL 
       AND StartTime < (Now - 0/0/0000 3: 00: 00 AM")

Answer 1

The condition AND EndTime = NULL always evaluates to unknown. Instead, use:

AND EndTime is NULL 

Then, you probably should tell the database how to parse the datetime string. This would work on MS Access (to require StartTime to be more than 3 hours in the past):

AND StartTime < dateadd('h',-3, now);

Per your comment, here is a full query:

SELECT TimeID 
FROM tblLunchTime 
WHERE ProductionID = 1 
AND EndTime IS NULL 
AND StartTime < DATEADD('h',-3,now)

The problem might be your prodId; I'm not sure what it's supposed to mean. You might try to use it as a variable, like

"WHERE ProductionID = " & prodId & " " & _

Answer 2

As I said in the other version of this question, I think you should calculate the date/time criterion before you concatenate. Otherwise, you're passing it off to the server to do. I would do the same even if the data store was Jet/ACE.