Use a VB variable in a SQL statement

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'.

concatenate the variable like so:

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

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

You need to concatenate it:

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