gromit987
11-19-2008, 04:21 AM
Hi all,
I am having difficulty making a dynamic time-date based sql query work. Here is my code:
Sheets("Recent Production Analysis").Range("F61").Value = GetDBValue("SELECT count(Batch_ID) FROM Production WHERE Batch_ID=" & myBatch & " and (Prod_Time > " & StartTime & ") and (Prod_Time < " & CurrentTime & ") and (Vision_Result=0)")
This produces a zero result.
StartTime is declared as a variant.
StartTime has a value of "2008-11-18 09:29"
CurrentTime is declared as variant.
CurrentTime has a value of "2008-11-18 09:39"
If I put a break in just after the query then I can see that the correct values are in StartTime and CurrentTime.
If I replace the StartTime and CurrentTime in the query with the same dates as above then I get a result. The code for this is:
Sheets("Recent Production Analysis").Range("F61").Value = GetDBValue("SELECT count(Batch_ID) FROM Production WHERE Batch_ID=" & myBatch & " and (Prod_Time > '2008-11-18 09:29') and (Prod_Time < '2008-11-18 09:39') and (Vision_Result=0)")
Can some please explain why this is and how to fix it?
:dunno:banghead:
thanks.
I am having difficulty making a dynamic time-date based sql query work. Here is my code:
Sheets("Recent Production Analysis").Range("F61").Value = GetDBValue("SELECT count(Batch_ID) FROM Production WHERE Batch_ID=" & myBatch & " and (Prod_Time > " & StartTime & ") and (Prod_Time < " & CurrentTime & ") and (Vision_Result=0)")
This produces a zero result.
StartTime is declared as a variant.
StartTime has a value of "2008-11-18 09:29"
CurrentTime is declared as variant.
CurrentTime has a value of "2008-11-18 09:39"
If I put a break in just after the query then I can see that the correct values are in StartTime and CurrentTime.
If I replace the StartTime and CurrentTime in the query with the same dates as above then I get a result. The code for this is:
Sheets("Recent Production Analysis").Range("F61").Value = GetDBValue("SELECT count(Batch_ID) FROM Production WHERE Batch_ID=" & myBatch & " and (Prod_Time > '2008-11-18 09:29') and (Prod_Time < '2008-11-18 09:39') and (Vision_Result=0)")
Can some please explain why this is and how to fix it?
:dunno:banghead:
thanks.