Criteria In A Query Not Evaluating In Code

[catbeasy]

The below code dumps data from one table into another using the query: qry_rpt_data_for_ct_prep_tbl as the intermediary to do so. It works, except that I wanted to restrict the data based on user selection for the time period.

So, I put criteria in the query itself. But with this addition, it blows up, telling me too few parameters, highlighting the "Set rs" portion of code.

Is there something I can do to make this work, or do I have to put the criteria in the recordset query string? If so any help on the syntax required for the string to read the form date values from a (date input) text box is appreciated..


Public Sub Build_Cross_Tab_Prep()
'order of fields below in the openrecordset are important as the 'for' statement goes in order the fields appear..
Dim rs As Recordset
Dim j As Integer
Dim str_SQL_Goal As String

Set rs = CurrentDb.OpenRecordset("qry_rpt_data_for_ct_prep_tbl")

rs.MoveLast
rs.MoveFirst

If rs.RecordCount > 0 Then

Do While Not rs.EOF

Dim i As Integer
i = 1

For i = 1 To 24
Dim strSQL As String
strSQL = "INSERT INTO tbl_Rpt_Crosstab_Prep (STATE, DT_RPT, QT_RPT, YR_RPT, MetricName, MetricValue) VALUES ('" _
+ rs!STATE + "',#" + Format(rs!DT_RPT, "MM/DD/YYYY") + "#,'" + CStr(QuarterFromDate(rs!DT_RPT)) + "','" _
+ CStr(Format(rs!DT_RPT, "yyyy")) + "','" + rs.Fields(i).Name + "'," + CStr(rs.Fields(i).Value) + ")"
CurrentDb.Execute strSQL

Next i
rs.MoveNext

Loop

End If

 

[boblarson]

What's the SQL for the query:

qry_rpt_data_for_ct_prep_ tbl

 

[catbeasy]

What's the SQL for the query:

qry_rpt_data_for_ct_prep_ tbl

SELECT tbl_Data.STATE, tbl_Data.DT_RPT, tbl_Data.Grp_Install_TAT AS [Group Installation TAT], tbl_Data.New_Grp_Install_Accuracy AS [New Group Installation Accuracy], tbl_Data.Grp_Renewal_Accuracy AS [Group Renewals Accuracy], tbl_Data.Member_Install_TAT_IFP_COM AS [Member Install TAT], tbl_Data.Enroll_TAT_IFP_COM AS [Enrollment TAT IFP/COM], tbl_Data.Enroll_Accuracy_IFP_COM AS [Enrollment Accuracy IFP COM], tbl_Data.Vertex_Enroll_Accuracy_IFP_COM AS [Vertexing Enrollment Accuracy], tbl_Data.ID_Card_TAT_IFP_COM AS [ID Card TAT (CDS)], tbl_Data.Enroll_TAT_HN_MEDICARE AS [Enrollment TAT Application Processing (HN to ABS/MC400)], tbl_Data.Enroll_TAT_Mbrship_MEDICARE AS [Enrollment TAT Application Processing (Membership to ABS/MC400)], tbl_Data.Enroll_Accuracy_MEDICARE AS [Enrollment Accuracy MEDICARE], tbl_Data.Disenroll_Accuracy_MEDICARE AS [Disenrollment Accuracy], tbl_Data.Enroll_TAT_MEDICAID AS [Enrollment TAT MEDICAID], tbl_Data.Enroll_Accuracy_MEDICAID AS [Enrollment Accuracy MEDICAID], tbl_Data.Bill_Timeliness_IFP_COM AS [Billing Timeliness], tbl_Data.AR_GT_90_IFP_COM AS [Accounts Receivable >90 days (Commercial Group)], tbl_Data.Mbrship_Recon_60_IFP_COM AS [Membership Reconciled within 60 days], tbl_Data.Days_In_AR_IFP_COM AS [Days in AR], tbl_Data.Prem_Recon_Cash_App_Accuracy_IFP_COM AS [Premium Reconciliation and Cash Application Accuracy], tbl_Data.AR_GT_90_MEDICARE AS [Accounts Receivable >90 days (Medicare)], tbl_Data.BRK_Appoint_Accuracy AS [Broker Appointments Accuracy], tbl_Data.BRK_Comm_Accuracy AS [Broker Commissions Accuracy], tbl_Data.BRK_Comm_Timeliness AS [Broker Commission Timeliness], tbl_Data.Sales_Comm_Timeliness AS [Sales Commission Timeliness]
FROM tbl_Data;

 

[CraigDolphin]

To resolve this I suspect you will need to wrap the criteria reference in your query in an Eval("") function.

So, instead of
[Form]![Formname]![ControlName]

you should use

Eval("[Form]![Formname]![ControlName]")

 

[pbaldy]

If you mean it blows up when you add a criteria to the query in the recordset:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[CraigDolphin]

Interesting link Paul. I've always managed to just use the eval function in the query itself to solve this issue, but it's always good to know another way to skin the same cat.

 

[boblarson]

I've also found that, at times (but not always) this error is caused simply by not passing the correct delimiters along with a form reference. So, for example, if you have a text field you are passing something from and forget to add quotes, or a date without octothorpes.

I've fixed several of these "Parameter Expected" errors simply by finding those errors. Now, that's not to say it is the problem for all of them, but something else to look at.

 

[catbeasy]

To resolve this I suspect you will need to wrap the criteria reference in your query in an Eval("") function.

So, instead of
[Form]![Formname]![ControlName]

you should use

Eval("[Form]![Formname]![ControlName]")

ok, this works, thanks for that function, didn't even know it existed..!

 

[CraigDolphin]

Happy I could help