Query that is used in the openrecordset method

[David9746]

I have a query that I am using in a sub procedure and I am opening a
recordset using that query as the recordsource. The query has a parameter
that references a field on the form that the sub procdure is run from.

Problem is I am getting an error message all the time that says too few
parameters expected 1. When you run this query on it's own it runs fine no
problem.

Can anyone help me on this.

 

[Dale Fye]

Can you post the SQL?

 

[David9746]

I can but it is a pretty large sql statement consisting of other querries.

I wiil post it a s soon my working partner is done to the daily backups

 

[Michel Walsh]

A query with a parameter like FORMS!formName!controlName will run fine in
the user interface, or as rowsource of a list box, or even as record source
of another form... but won't run if you NEED to use CurrentDb to work with
it.


See http://www.mvps.org/access/queries/qry0013.htm for some more details.



Vanderghast, Access MVP

 

[David9746]

SELECT Date() AS [Report Date],
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod,
q_Rdy_ConvDate_ClientCateg_Summ.Clients, Null AS Profiled, Null AS
ProvidedDoc, Null AS [Not submitted], Null AS IBTfund,
q_Rdy_ConvDate_BeneCateg_Summ.SSCfund, [SSCfund]-[NotExported] AS
BeneExported, q_Rdy_ConvDate_BeneCateg_Summ.NotExported AS BeneNotExported,
([SSCfund]-[NotExported])/[SSCfund] AS BeneExportComplete,
[NotExported]/[SSCfund] AS BeneExportRemaining, [SSCfund]-[GBEpend] AS
GBEComplete, ([SSCfund]-[GBEpend])/[SSCfund] AS GBEsetupComplete,
q_Rdy_ConvDate_BeneCateg_Summ.GBEpend, [GBEpend]/[SSCfund] AS
GBEsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.TotMkts,
q_Rdy_ConvDate_MarketCateg_Summ.TotDocMkts, [TotMkts]-[MktNotExported] AS
MktsExported, IIf([TotMkts]=0,1,([TotMkts]-[MktNotExported])/[TotMkts]) AS
MktExportComplete, q_Rdy_ConvDate_MarketCateg_Summ.MktNotExported,
IIf([TotMkts]=0,1,[MktNotExported]/[TotMkts]) AS MktExportRemaining,
[TotMkts]-[MktNotExported] AS [AOM Recvd],
([TotMkts]-[MktNotExported])-[LACpend] AS LACcomplete,
IIf([TotMkts]-[MktNotExported]=0,1,(([TotMkts]-[MktNotExported])-[LACpend])/([TotMkts]-[MktNotExported]))
AS LACsetupComplete, Null AS Cash, q_Rdy_ConvDate_MarketCateg_Summ.LACpend,
IIf(([TotMkts]-[MktNotExported])=0,1,[LACpend]/([TotMkts]-[MktNotExported]))
AS LACsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.LACpendDocMkt,
IIf(([TotDocMkts]-[MktNotExported])=0,1,[LACpendDocMkt]/([TotDocMkts]-[MktNotExported]))
AS DocMktLACsetupRemaining, IIf([Forms]![Menu: Canned
Reports]![cmb_NewMktBeneRecords]="Yes","New Mkts","No") AS NewMkts
FROM (q_Rdy_ConvDate_ClientCateg_Summ INNER JOIN
q_Rdy_ConvDate_BeneCateg_Summ ON
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod) LEFT JOIN
q_Rdy_ConvDate_MarketCateg_Summ ON
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_MarketCateg_Summ.conv_date_mod
ORDER BY q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod;

 

[Dale Fye]

David,

Try declaring the datatype of the parameter (form control) that you are
passing the query. You can do it by going into the SQL view and pasting the
following at the beginning of the SQL statement:

PARAMETERS [Forms]![Menu: Canned Reports]![cmb_NewMktBeneRecords] Text
(255);
SELECT ....

Make sure there is a semi-colon at the end of the Parameters line and the
SELECT statement begins on the line immediately following the PARAMETERS
line.

HTH
Dale

SELECT Date() AS [Report Date],
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod,
q_Rdy_ConvDate_ClientCateg_Summ.Clients, Null AS Profiled, Null AS
ProvidedDoc, Null AS [Not submitted], Null AS IBTfund,
q_Rdy_ConvDate_BeneCateg_Summ.SSCfund, [SSCfund]-[NotExported] AS
BeneExported, q_Rdy_ConvDate_BeneCateg_Summ.NotExported AS
BeneNotExported,
([SSCfund]-[NotExported])/[SSCfund] AS BeneExportComplete,
[NotExported]/[SSCfund] AS BeneExportRemaining, [SSCfund]-[GBEpend] AS
GBEComplete, ([SSCfund]-[GBEpend])/[SSCfund] AS GBEsetupComplete,
q_Rdy_ConvDate_BeneCateg_Summ.GBEpend, [GBEpend]/[SSCfund] AS
GBEsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.TotMkts,
q_Rdy_ConvDate_MarketCateg_Summ.TotDocMkts, [TotMkts]-[MktNotExported] AS
MktsExported, IIf([TotMkts]=0,1,([TotMkts]-[MktNotExported])/[TotMkts]) AS
MktExportComplete, q_Rdy_ConvDate_MarketCateg_Summ.MktNotExported,
IIf([TotMkts]=0,1,[MktNotExported]/[TotMkts]) AS MktExportRemaining,
[TotMkts]-[MktNotExported] AS [AOM Recvd],
([TotMkts]-[MktNotExported])-[LACpend] AS LACcomplete,
IIf([TotMkts]-[MktNotExported]=0,1,(([TotMkts]-[MktNotExported])-[LACpend])/([TotMkts]-[MktNotExported]))
AS LACsetupComplete, Null AS Cash,
q_Rdy_ConvDate_MarketCateg_Summ.LACpend,
IIf(([TotMkts]-[MktNotExported])=0,1,[LACpend]/([TotMkts]-[MktNotExported]))
AS LACsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.LACpendDocMkt,
IIf(([TotDocMkts]-[MktNotExported])=0,1,[LACpendDocMkt]/([TotDocMkts]-[MktNotExported]))
AS DocMktLACsetupRemaining, IIf([Forms]![Menu: Canned
Reports]![cmb_NewMktBeneRecords]="Yes","New Mkts","No") AS NewMkts
FROM (q_Rdy_ConvDate_ClientCateg_Summ INNER JOIN
q_Rdy_ConvDate_BeneCateg_Summ ON
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod) LEFT JOIN
q_Rdy_ConvDate_MarketCateg_Summ ON
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_MarketCateg_Summ.conv_date_mod
ORDER BY q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod;

Can you post the SQL?
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.