A RecordSet based on a Union Queries

[Paul H]

I have 4 queries that build on each other qryTable1 reads Table1, qryTable2 reads Table2, qryUnion combines the two and qry4 joins qryUnion with 3 other tables and appends it to tblData. I can run the queries as they are and get the results I need, but I would like to move part of the process into a DAO recordset based on the SQL in qry4 and I've built a script that does that, almost. When I try to open the recordset I get runtime error 3061 Too few parameters. Expected 4.

Well there are 4 parameters, text boxes on a form, and I've declared them specifically everywhere I use them qryTable1, qryTable2, and in the SQL script in my procedure. I've even replaces the parameters with variables in the script. I know the SQL is valid because I've copies it from the Immediate window and plugged it into a query. I'm nearly convinced this can't be done, but before I give up I thought I would toss it out there. Here's how I've declared the parameters in the script.

   SQL1 = "PARAMETERS [Forms]![frm_DowntimeReasons]![txt_FromDate] DateTime, "  & _
[Forms]![frm_DowntimeReasons]![txt_TillDate] DateTime, " & _
"[Forms]![frm_DowntimeReasons]![txt_FromTime] DateTime,  "  & _
[Forms]![frm_DowntimeReasons]![txt_tillTime] DateTime; "

I've also pasted this SQL into the first two queries.

PARAMETERS Forms!frm_DowntimeReasons![txt_FromDate] DateTime, Forms!frm_DowntimeReasons![txt_FromTime] DateTime, 
Forms!frm_DowntimeReasons![txt_TillDate] DateTime, Forms!frm_DowntimeReasons![txt_TillTime] DateTime;

Everything runs perfectly except when I try to open the recordset.

[pbaldy]

Well, you're running into this:

http://support.microsoft.com/default...b;en-us;209203

An alternative that often works is wrapping each form reference in the query in the Eval() function. If you're building the SQL in code (I can't tell for sure), you would concatenate the form reference in, along the lines of:

SQL1 = "...WHERE DateField Between #" & Forms!frm_DowntimeReasons![txt_FromDate] & "# And #" & Forms![frm_DowntimeReasons]![txt_TillDate] & "#"

[Paul H]

I (re)built this who thing in code to mimic an existing (functioning) query. I've hit this from every angle I can think of. My only conclusion from this is that some things are just meant not to be, or just not worth the effort. I'm going to let Access do what it does best and move on.

Thanks anyway,

Paul

[pbaldy]

No problem, though I think Access could have done it just fine.