Too few parameters

[boblarson]

I'm trying to assign a pre-built query (which is built from other queries) to a DAO recordsource, but I keep getting the "too few parameters, expected 2" error.

I suspect it has to do with the fact that in one of the preceding queries I am narrowing down data via Max on date and also the value between two dates (from text boxes). But, I can't figure out how to assign the parameters to the recordset so it will open.

my code to open the recordsource is:
Set rstDemo = db1.OpenRecordset("04 qryAsthma_Ethnicity_Totals")

The SQL behind that query is:

SELECT tlkpEthnicity.EthRollup, Count([04 qryAsthma_Ethnicity].CountOfEthnicity) AS TotalEthnic
FROM [04 qryAsthma_Ethnicity] INNER JOIN tlkpEthnicity ON [04 qryAsthma_Ethnicity].Ethnic = tlkpEthnicity.Ethnicity
GROUP BY tlkpEthnicity.EthRollup;

and that query is built upon:
PARAMETERS Forms!frmReportsMain!txtBegDate DateTime, Forms!frmReportsMain!txtEndDate DateTime;
SELECT [00 qry_Asthma_At_Last_Visit].clinic_code, [00 qry_Asthma_At_Last_Visit].ethnicity AS Ethnic, 1 AS CountOfEthnicity, [00 qry_Asthma_At_Last_Visit].MaxOfvisit_date AS VDate
FROM [00 qry_Asthma_At_Last_Visit]
WHERE ((([00 qry_Asthma_At_Last_Visit].MaxOfvisit_date) Between [Forms]![frmReportsMain]![txtBegDate] And [Forms]![frmReportsMain]![txtEndDate]));

and finally, that one is built on:

SELECT tblVisits.clinic_code, tblVisits.chart_number, Max(tblVisits.visit_date) AS MaxOfvisit_date, tblVisits.hlth_asthma, tblDemo.ethnicity
FROM tblVisits LEFT JOIN tblDemo ON (tblVisits.chart_number = tblDemo.chart_number) AND (tblVisits.clinic_code = tblDemo.clinic_code)
GROUP BY tblVisits.clinic_code, tblVisits.chart_number, tblVisits.hlth_asthma, tblDemo.ethnicity
HAVING (((tblVisits.hlth_asthma)=True));

Any assistance with how to run parameterized queries from VBA would be great.

Thanks,

Bob Larson

 

[dcx693]

These are the two parameters you need to specify: Forms!frmReportsMain!txtBegDate DateTime and Forms!frmReportsMain!txtEndDate DateTime.

In order to tell VBA where they are, you need something like this (assuming you're using DAO):

Dim qdf as DAO.QueryDef
Set qdf=dbs.QueryDefs("04 qryAsthma_Ethnicity_Totals")

qdf("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate DateTime
qdf("Forms!frmReportsMain!txtEndDate DateTime")=Forms!frmReportsMain!txtEndDate DateTime
Set rst=qdf.OpenRecordset
[i]etc...[/i]

 

[boblarson]

You wrote:

code:--------------------------------------------------------------------------------
Dim qdf as DAO.QueryDef
Set qdf=dbs.QueryDefs("04 qryAsthma_Ethnicity_Totals")

qdf("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate DateTime
qdf("Forms!frmReportsMain!txtEndDate DateTime")=Forms!frmReportsMain!txtEndDate DateTime
Set rst=qdf.OpenRecordset

When I try using it, this part:

qdf("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate DateTime

shows up in red as if there is a syntax error.

So I tried modifying it to

qdf.Parameters("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate DateTime

but I get a syntax error about the DateTime part on the right (it says "Expected End of Statement"). So I tried just removing the DateTime part from the right hand side and it then gave me a Item not found in collection error.

So, I'm not sure how to fix the statement

qdf("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate DateTime

to make it work. I've not done a lot of work with DAO and/or parameterized queries, so I'm struggling a bit. I have done more work with ADO (can't change to ADO due to issues outside of my control) and I've done nothing with VBA and parameterized queries.

Thanks for the assistance and I hope that, with a little instruction and tweaking, I can get this going.

Thanks,

Bob Larson

 

[dcx693]

You know, I didn't notice something until just now. The "Date Time" part after the parameters as in:
PARAMETERS Forms!frmReportsMain!txtBegDate DateTime.

I think you can safely take those out and use expressions of the form:
qdf("Forms!frmReportsMain!txtBegDate DateTime")=Forms!frmReportsMain!txtBegDate. Can you try that and let me know?

 

[boblarson]

Thanks for the help. I had tried that and it didn't work. But I happened to find some code on the Dev Ashish site: The Access Web

Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Set qdf = db1.QueryDefs("04 qryAsthma_Ethnicity_Totals")

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

Set rstDemo = qdf.OpenRecordset(dbOpenDynaset)

That got me going. But thanks for the assistance. I know that there's sometimes several ways of making something work. I love this bulletin board. It has helped me so many times over the years. It's great to have such great people who want to help others out. I love being able to answer the questions that I've run across for something that I have learned as well.

H A P P Y T H U R S D A Y ! !


Bob Larson

 

[dcx693]

Good for you! Glad you found a solution.

 

[hair]

boblarson, you just made my day
is is working so nice your piece of code
until now I was doing huge stupid loops to get this in DAO
thanks