Passing a variable to a query

  • Thread starter Thread starter Ralph
  • Start date Start date
R

Ralph

I am trying to set up a module that lets the operator
enter a month as a text variable in order to run a query
that gets data from an annual budget table where the
columns in the table are
named "July", "August", "September" etc. Each month they
would enter the month needed and the query would process.
I tried building the query and using the "var" function
for the column name, then I built a module and defined the
variable to the name in the query but I get the error "too
few paramters. Expected 1" I am using QUERYdefs to assign
and run the query. It works fine if I don't try to use a
variable. Any ideas (besides 'give-up)(Ha!) would be
appreciated. Many thanks!!!!!!!!!
 
I tried building the query and using the "var" function
for the column name, then I built a module and defined the
variable to the name in the query but I get the error "too
few paramters. Expected 1"
Click to expand...

This error nearly always means a misspelled field name, or else a variable
value passed where a variable name was wanted. It would help if you posted
the SQL text of the query.
I am using QUERYdefs to assign
and run the query. It works fine if I don't try to use a
variable.
Click to expand...

Aha: there are lots of things that the Access GUI does in interpreting a
query before handing it off (like looking up form/ control values, for
example) that VBA and Jet simply bypass. We definitely need to see the SQL
view.

Hope that helps


Tim F
 
If you try to run a query in code that needs a value from the user,
you will get this error.

You need to set the parameters in Code as well
qdf.Parameters(strMyParam) = varValue
qdf.execute

If, as you seem to imply, you are building the query on the fly,
simply plug the value into the string as you build it. It will run
slower this way, ansd if you do this often, the code will be a
nightmare to maintain, but it WILL work.

strSQL = "Select " & me.ColumnName & " from tbl"
set rst = db.OpenRecordSet(strSQL)

While I am typiing I think Maybe ralph is trying thus, but is
forgetting to pull the Control out of the string
strSQL = "Select me.ColumnName from tbl"
will generate the error you describe as the DB Engine doesn't know
about me.ColumnName and assumes its a parameter.

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
 
Back
Top