SQL Dynamic Query in vba

[gromit987]

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.

[Bob Phillips]

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)")

[gromit987]

Thanks but the query with the specified static date-time works ok. The section with the dynamic CurrentTime and StartTime does not. Any suggestions for this section would be great, thanks.

[Bob Phillips]

If you look at what I posted, you will see the trick is to include the date/time in #...#, and format at as a string yyyy-mm-dd hh:mm

[gromit987]

This code works fine:

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)")

Your suggestion does not:

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)")

The problem is with this 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)")

The StartTime and CurrentTime need to be changable depending on the input from the spreadsheet.
I tried putting #'s around them but this did not work.
If I set the values for StartTime and CurrentTime to an integer then I get some results!

[Bob Phillips]

I repeat

... and format at as a string yyyy-mm-dd hh:mm

[gromit987]

How?

[lifeson]

Try this

Add a ' before your quote mark around the variables so it reads
'2008-11-18 09:39' and not 2008-11-18 09:39

("SELECT count(Batch_ID) " & _
"FROM Production " & _
"WHERE Batch_ID=" & myBatch & " " & _
"and (Prod_Time > '" & StartTime & "') " & _
"and (Prod_Time < '" & CurrentTime & "') " & _
"and (Vision_Result=0)")

[gromit987]

Thanks a million lifeson, you got it in one.

Thank you too xld.

:clap::clap::clap:

[lifeson]

Thanks a million lifeson, you got it in one.

Thank you too xld.

:clap::clap::clap:

Blo*dy H3ll Thats a first, :bug: :bug:
I can't believe I have got a suggestion right! :rotlaugh: :rotlaugh:

Seriously though this is great site if used correctly and its great to be able to share some of the knowledge that I have picked up from the guys on here