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

Tahbaza · Accepted Answer · 2010-06-30 16:21:08Z

1

concatenate the variable like so:

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

answered Jun 30, 2010 at 16:21

Tahbaza

9,5542 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

renick Over a year ago

Relax a bit. VB is typed. How exactly would you inject SQL in an int ?

StingyJack Over a year ago

This is probably the pattern that is repeated elsewhere, with strings.

StingyJack · Accepted Answer · 2010-06-30 16:21:21Z

1

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

answered Jun 30, 2010 at 16:21

StingyJack

19.6k11 gold badges69 silver badges127 bronze badges

Comments

Evan Trimboli · Accepted Answer · 2010-06-30 16:22:49Z

0

You need to concatenate it:

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

answered Jun 30, 2010 at 16:22

Evan Trimboli

30.1k6 gold badges49 silver badges68 bronze badges

Collectives™ on Stack Overflow

Use a VB variable in a SQL statement

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related