Solved: Help needed opening recordset with a parameter

[andysharps]

Hi,

I am having problems with the following bit of code, I am building up a string with a variable in & using that string to open a table as follows:


Criteria = "SELECT * FROM TransactionTable Where TransAgreementNumber = " & MyAgreementNumber

Set TransRS = CurrentDb.OpenRecordset(Criteria)

When I run that code the application stops at the Set TransRS line with the error message "Too few parameters, expected 1"

If I take the parameter out & just have

Criteria = "SELECT * FROM TransactionTable "

Set TransRS = CurrentDb.OpenRecordset(Criteria)

The application carries on & works fine.

Can somebody help me & explain where I'm going wrong?

Thank You

[hansup]

Criteria = "SELECT * FROM TransactionTable Where TransAgreementNumber = " & MyAgreementNumber

Set TransRS = CurrentDb.OpenRecordset(Criteria)

When I run that code the application stops at the Set TransRS line with the error message "Too few parameters, expected 1"
Check the content of your Criteria variable by adding this line just before the Set TransRS statement:
Debug.Print "Criteria: " & Chr(39) & Criteria & Chr(39) You can see what it prints by using the Ctrl+G keyboard shortcut to go to the Immediate Window of the VBA code editor.

Good luck,
Hans

[andysharps]

Thanks for the reply.

I've done as you suggested the contents of criteria are as I would expect, that being the Select statement followed by a valid value in the where clause.

[hansup]

I've done as you suggested the contents of criteria are as I would expect, that being the Select statement followed by a valid value in the where clause. OK, one down!

You showed us this SQL statement executes without error:

Criteria = "SELECT * FROM TransactionTable "

So try revising it like this:

Criteria = "SELECT TransAgreementNumber FROM TransactionTable "

If it complains again about "too few parameters", check the spelling of the field name in TransactionTable.

Hans

[andysharps]

"SELECT TransAgreementNumber FROM TransactionTable " works fine but if I amend that to be

"SELECT TransAgreementNumber FROM TransactionTable where TransAgreementNumber = MyAgreementNumber" then again it stops with the same error 'too few parameters,1 expected'

So, it's fine without a parameter but gives the error whenever I try adding a parameter

[stanl]

maybe

'SELECT TransAgreementNumber FROM TransactionTable where TransAgreementNumber = "MyAgreementNumber"'

[andysharps]

Thanks but no that doesn't work, that uses "agreementnumber" literally instead of the value it is storing, (it returns the same error by the way).

Reading other forums it looks like Jet can't evaluate the string with a parameter concatenated onto the end, but I can't get my head round what I need to do to resolve it!

[CreganTur]

The first issue I notice, is that you are not providing data type qualifiers in your criteria string. If MyAgreementNumber is a sting value, then you need to wrap it with single quote string qualifiers. Like this:
Criteria = "SELECT * FROM TransactionTable Where TransAgreementNumber = '" & MyAgreementNumber & "'"

If this does not fix your issue, then I would suggest using ADO instead of DAO... but there's no reason why this should not work in DAO.

HTH:thumb

[hansup]

"SELECT TransAgreementNumber FROM TransactionTable where TransAgreementNumber = MyAgreementNumber" then again it stops with the same error 'too few parameters,1 expected' Is that the actual statement (in the Criteria variable) you're submitting to Currentdb.OpenRecordset?

To eliminate any confusion, please show us (copy and paste) exactly what the Immediate Window shows you in response to the line you added earlier:

Debug.Print "Criteria: " & Chr(39) & Criteria & Chr(39)

Hans

[andysharps]

CreganTur, many thanks for your help, that has solved it.

Thanks to everybody for their help.