Using SQL Statement in VBA

[rvermin]

I have a code that looks like this:

Dim db As Database
Dim rs As Recordset
Dim StrsQL As String
Dim SvAr As String

Set db = CurrentDb

SvAr ="A" 'not really "A", actually a value from control, but for ease of explanation I use "A"

StrsQL = "SELECT * From [Table1] Where ([Category] = (SvAr))"

Set rs = db.OpenRecordset(StrsQL, dbOpenSnapshot)


.... some mor e code follows.

Now, why isn't this SqL statement working?

It gives the error message "RunTime error 3061, too few Parameters. Expected 1."

Please help.

Ramon

 

[jrbarnett]

Ramon,

I think that Access is getting confused between the DAO and ADO libraries. Firstly, go to Tools -> REferences and make sure there is a tick in the box next to "Microsoft DAO 3.6 Object Library" and then change your code to read:

Dim db As DAO.Database
Dim rs As DAO.Recordset

This should be enough to fix your problem.

John

 

[FancyPrairie]

Your code should be:

StrsQL = "SELECT * From [Table1] Where ([Category] = " & A & &quot"

If it's a text string then it should read

StrsQL = "SELECT * From [Table1] Where ([Category] = '" & A & "')"

 

[rvermin]

Many thanks Both of You!!!
It Works! 3.6 wasn't registered and " " + the ' worked!