Error 3061 - Too few parameters - expected 1. (1 Viewer)

[mikewood1980]

Hi

When executing the following SQL statement in VBA...

Private Function createAdverseDeviationsString(strTestID As String) As String

        Dim dbcurr As Database
        Set dbcurr = DBEngine.Workspaces(0).Databases(0)
        
        Dim sQuery As String
        sQuery = "select * from tblAdverseDeviations where ((tblAdverseDeviations.chrTestID)=" & strTestID & ")"
        
        Dim rsADTable As Recordset
        Set rsADTable = dbcurr.OpenRecordset(sQuery)
        
        Dim testFound As Boolean
        Dim count As Integer
        count = 0

...etc

I get the following error...

Error 3061 - Too few parameters. Expected 1.

This occurs on the line:

 Set rsADTable = dbcurr.OpenRecordset(sQuery)

If anyone can help that would be great (I've not really used SQL statements that much before so I am a bit of a novice!)

Thanks in advance
Mike Wood

 

[khawar]

sQuery = "select * from tblAdverseDeviations where ((tblAdverseDeviations.chrTestID)='" & strTestID & "')"

 

[namliam]

FYI

Strings you have to enclose with ' and dates with #, numbers you dont need to enclose.
So i.e. if your strTestID is a string:
sQuery = "select * from tblAdverseDeviations where ((tblAdverseDeviations.chrTestID)='" & strTestID & "')" (What khawar said)
strTestID is a date:
sQuery = "select * from tblAdverseDeviations where ((tblAdverseDeviations.chrTestID)=#" & strTestID & "#)"
strTestID is a number:
sQuery = "select * from tblAdverseDeviations where ((tblAdverseDeviations.chrTestID)=" & strTestID & ")" (what you had...)

Happy coding

 

[mikewood1980]

Thanks guys - that really helped!
Mike
PS thanks for the tips Namliam!

 

[Darth Vodka]

you should use the debug window when the code debugs and type

?sQuery

then copy and paste this into query design and see if there's any faults in the SQL

you'll find yourself doing that ALL the time