SQL results in the dreaded 3061 "Too few parameters, expected 1" error

[markjkubicki]

...and I just can't get to why (to the point of frustration -which leads me to believe it's right under my keys...)

      
               Dim vOptionNumber As Long
               Dim dbs As DAO.Database
               Dim rst As DAO.Recordset

               vOptionNumber = Nz([cmbInstallationNoteTitle], 0) + Nz([cmbInstallationNoteTitle_LongList], 0) 
                                       ' results in a valid number
                                      
                strSQL = "SELECT * FROM [tbeInstallationNotes] WHERE [InstallationNote_OptionNumber] = vOptionNumber; " 
                            ' tbeInstallationNotes is a properly spelled valid table
                            ' InstallationNote_OptionNumber is a properly spelled field with a number data type

                                     
                Set dbs = CurrentDb()
                Set rst = dbs.OpenRecordset(strSQL)
                             ' and of course this is where the error occurs
                    With rst
                    DoCmd.GoToRecord , "", acFirst
                    Do Until .EOF
                    <...>

any suggestions at all will be greatly appreciated in advance,
thnx
mark

[pbaldy]

You have to concatenate the variable into the string.

strSQL = "SELECT * FROM [tbeInstallationNotes] WHERE [InstallationNote_OptionNumber] = " & vOptionNumber

[ssanfu]

You MUST concatenate the number (vOptionNumber) to the SQL string.

Try this - Add the line in BLUE (your code):

               Dim vOptionNumber As Long
               Dim dbs As DAO.Database
               Dim rst As DAO.Recordset

               vOptionNumber = Nz([cmbInstallationNoteTitle], 0) + Nz([cmbInstallationNoteTitle_LongList], 0) 
                                       ' results in a valid number
                                      
                strSQL = "SELECT * FROM [tbeInstallationNotes] WHERE [InstallationNote_OptionNumber] = vOptionNumber; " 
                            ' tbeInstallationNotes is a properly spelled valid table
                            ' InstallationNote_OptionNumber is a properly spelled field with a number data type

               Debug.Print strSQL

               'Set a breakpoint on the next line so you can see the immediate window
                Set dbs = CurrentDb()
                Set rst = dbs.OpenRecordset(strSQL)
                             ' and of course this is where the error occurs
                    With rst
                    DoCmd.GoToRecord , "", acFirst
                    Do Until .EOF
                    <...>

Look at the immediate window.

Now change the code to this (modified code):

               Dim vOptionNumber As Long
               Dim dbs As DAO.Database
               Dim rst As DAO.Recordset

               vOptionNumber = Nz([cmbInstallationNoteTitle], 0) + Nz([cmbInstallationNoteTitle_LongList], 0) 
                                       ' results in a valid number
                                      
                strSQL = "SELECT * FROM [tbeInstallationNotes] WHERE [InstallationNote_OptionNumber] = " & vOptionNumber & ";" 
                            ' tbeInstallationNotes is a properly spelled valid table
                            ' InstallationNote_OptionNumber is a properly spelled field with a number data type

               Debug.Print strSQL

               'Set a breakpoint on the next line so you can see the immediate window                                     
                Set dbs = CurrentDb()
                Set rst = dbs.OpenRecordset(strSQL)
                             ' and of course this is where the error occurs
                    With rst
                    DoCmd.GoToRecord , "", acFirst
                    Do Until .EOF
                    <...>

Look at the immediate window. What is the difference???

[markjkubicki]

got it
thnx (learn something new every day); (and sometimes something old... as long as "something is learned!)