Too few Parameters. Expected 1

  • Thread starter Thread starter Paul C
  • Start date Start date
P

Paul C

Hi,

I'm trying to export a union query to a Fixed Width File.
The query refers to a control on a form for one of its
parameters. When the Export Wizard starts I get an
error "Too few Parameters. Expected 1".

I think this problem relates to the way Jet handles form
referenced parameters but I can't find any details as to
how the problem is solved. Can anyone give me some advice?

Thanks

A summary of the query is as follows:

SELECT ChqID, SupAccountCode, Right([DatePaid],4)+Mid
([DatePaid],4,2)+Left([DatePaid],2) AS
TransactionDate, "Debit" as DCMarker
From tblInvoices
Where SupAccountCode = [Forms]![ParamForm]![ParamControl]
Union All SELECT ChqID, SupAccountCode,
Right([DatePaid],4)+Mid([DatePaid],4,2)+Left([DatePaid],2)
AS TransactionDate, "Credit" as DCmarker
from tblInvoices
Where SupAccountCode = [Forms]![ParamForm]![ParamControl]
 
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Dim prm As Parameter

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

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError
 
Back
Top