Query based on query based on query based on form (2003)

Topic

New Reply

I have a form that has 3 text fields with dates in them.

I have a query that uses those dates to calculate the age of something. Call it query1.

I have a query that is based on query1 that gets the count of distinct item numbers & some other fields. Call it query 2.

I have another query, based on query 2, that sums the $$ fields. Call it query3.

Queries 1 & 2 work just fine, but query 3 gives me an error that the jet engine does not recognize the items from my form. ?????

They are not referenced in query 3, only query1, which works.

Any pointers?

Thanks!

Reply | Quote

Replies

Use the Eval function in query1 when referencing fields on a form. So in your query, instead of something like this (I’m guessing at form and control names):

Forms!frmSomething!txtDate1

Use this instead:

Eval(“Forms!frmSomething!txtDate1”)

Notice that you encase the entire reference string in quotes.

Reply | Quote

Another possibility is to declare the references to the form text boxes as parameters for the each of the queries:
– In design view, select Query | Parameters…
– Enter a reference as used in the first query, e.g. [Forms]![NameOfForm]![NameOfTextbox], and the corresponding data type.
– Repeat for the other text boxes.
– Click OK.

Reply | Quote

Hans, I tried your suggestion. Now when I try to open the query in VBA, I get the following error:
Run-time error 3061
Too Few Parameters. Expected 0.

This is the VBA in question:
Set rstDetails = db.OpenRecordset(“SELECT * FROM [qryReportA-PO_Count] WHERE BU = ‘” & rstBU!BU & “‘”, dbOpenForwardOnly)

Reply | Quote

I’d try Mark’s suggestion first. If that doesn’t help, could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote