Use a VB variable in a SQL statement

Question

I'm trying to create a sql statement but I require the use of a VB variable. The problem is, I keep getting an error about too few parameters when I try to just put the variable in. Is there some sort of format I need to use in order to add a VB variable into a SQL statement?

Set rs = CurrentDb.OpenRecordset("SELECT StartTime " & _
            "FROM tblLunchTime " & _
            "WHERE TimeID = (SELECT max(TimeID-count) FROM tblLunchTime);")

The variable in this situation is 'count'.

Answer 1

concatenate the variable like so:

Set rs = CurrentDb.OpenRecordset("SELECT StartTime " & _
            "FROM tblLunchTime " & _
            "WHERE TimeID = (SELECT max(TimeID-" & count & ") FROM tblLunchTime);")

Answer 2

Well... using non-parameterized sql like you want to is usually a very bad idea. There are many articles on how to parameterize a sql query or use stored procs for VB (6 and .NET).

Answer 3

You need to concatenate it:

Set rs = CurrentDb.OpenRecordset("SELECT StartTime " & _
            "FROM tblLunchTime " & _
            "WHERE TimeID = (SELECT max(TimeID-" & count & ") FROM tblLunchTime);")