Too Few Parameters Expected 3 Error 3061 (1 Viewer)

patkeaveney

Registered User.
Local time
Today, 08:23
Joined
Oct 12, 2005
Messages
75
The SQL below is taken stright from the SQL view of the query.

When trying to use it as an SQL statement I get the above error:

Set rs = CurrentDb.OpenRecordset(SQL_String)

SQL_String = "SELECT Count([intFOIRequestID]) AS RecCountAll FROM [SELECT DISTINCT intFOIRequestID FROM qrySearchByExemption]. AS RecCount;"

The query runs fine in access

I am trying to use the value "count" from the query to use in a mesage box on a form

Any help greatly appriciated.

Pat
 

Dennisk

AWF VIP
Local time
Today, 08:23
Joined
Jul 22, 2004
Messages
1,649
do you have the lines of code in the correct order

Assign SQL_String
then
your open statement
 

patkeaveney

Registered User.
Local time
Today, 08:23
Joined
Oct 12, 2005
Messages
75
I think so, see below.

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

SQL_String = "SELECT Count([intFOIRequestID]) AS RecCountAll FROM [SELECT DISTINCT intFOIRequestID FROM qrySearchByExemption]. AS RecCount;"

Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(SQL_String)
 

MSAccessRookie

AWF VIP
Local time
Today, 04:23
Joined
May 2, 2008
Messages
3,428
I think so, see below.

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

SQL_String = "SELECT Count([intFOIRequestID]) AS RecCountAll FROM [SELECT DISTINCT intFOIRequestID FROM qrySearchByExemption]. AS RecCount;"

Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(SQL_String)

The format for a SubQuery is different between VB and Access Jet. Try changing [SELECT to (SELECT, and ]. to ). in the VB Code and see if that makes any difference.
 

patkeaveney

Registered User.
Local time
Today, 08:23
Joined
Oct 12, 2005
Messages
75
Thanks for the advice.
Made the changes you suggested an now get the following error

syntax error in FROM clause

qrySearchByExemption referenced in the above sql has parmeters from a form in it.

Will this be the cause of the origianl error?

If so any ideas on how to resolve it

Pat
 

WIS

Registered User.
Local time
Today, 19:23
Joined
Jan 22, 2005
Messages
170
Thanks for the advice.
Made the changes you suggested an now get the following error

syntax error in FROM clause

qrySearchByExemption referenced in the above sql has parmeters from a form in it.

Will this be the cause of the origianl error?

If so any ideas on how to resolve it

Pat

This one is a classic - there is an easy workaround. I got this from "Access97 Developers Handbook" Litwin, Getz et al.

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySomethingWithParameters")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

I have used this on multiple occasions - it will handle from no parameters to as many as you like. I have saved pages of programming as I've been able to get a recordset based on qrys ( many of them cascaded) instead of multiple If and Case statements in code.
 

patkeaveney

Registered User.
Local time
Today, 08:23
Joined
Oct 12, 2005
Messages
75
Wis

Thank you so much for your reply.
Your solution is so simple.
I have spent days trying to resolve this problem.

You are a STAR...................

Thanks

Pat
 

Users who are viewing this thread

Top Bottom