MS access "No values given for one or more parameters"

[Mansish Prasher]

i have an ongoing error in ms access vba, being pretty new to the app i am stuck, i have a split database where in i am trying to execute a query in vba but it is throwing the error"No values found for one or more given parameter" however when i debug.print the query and run in ms access it works fine,



below is the query, can you help:

qry = "SELECT * FROM Core"
qry1 = qry & " WHERE (((Core.Activity='" & UCase([Forms]![frmReports]![cmbrptactivity]) & "') AND (Core.Unit='" & UCase([Forms]![frmReports]![cmbunit]) & "') AND (Core.Dt_Upload Between #" & [Forms]![frmReports]![txtDate1] & "# And #" & [Forms]![frmReports]![txtDate2] & "#)))"
qry2 = qry1 & " OR (((Core.Activity='" & UCase([Forms]![frmReports]![cmbrptactivity]) & "') AND (([Forms]![frmreports]![cmbunit]) Is Null) AND (Core.Dt_Upload Between #" & [Forms]![frmReports]![txtDate1] & "# And #" & [Forms]![frmReports]![txtDate2] & "#)))"
qry3 = qry2 & " OR ((((Core.Unit)='" & UCase([Forms]![frmReports]![cmbunit]) & "') AND (([Forms]![frmreports]![cmbrptactivity]) Is Null) AND ((Core.Dt_Upload) Between (#" & [Forms]![frmReports]![txtDate1] & "#) And (#" & [Forms]![frmReports]![txtDate2] & "#))))"
qry4 = qry3 & " OR (((([Forms]![frmreports]![cmbunit]) Is Null) AND (([Forms]![frmreports]![cmbrptactivity]) Is Null) AND ((Core.Dt_Upload) Between (#" & [Forms]![frmReports]![txtDate1] & "#) And (#" & [Forms]![frmReports]![txtDate2] & "#))))"

rs.Open qry4, cn, adOpenKeyset, adLockOptimistic

[June7]

Is Core a table or a query with dynamic parameters?

[Mansish Prasher]

Core is a master table with all the data, there is user form frmReports also acting as a search form. I am allowing users to pass either one or multiple parameters to refine the data, once the data is filtered i would be inputting this data to a new temp table which inturn will populate the report. This is the approach i am following.If you have a better approach it will be highly appreciated. The above mentioned query works fine if i execute in ms access but not if i execute it through vba.

[June7]

Not sure what this criteria does for filtering records nor why it is within quote marks. Maybe this is cause of the issue. I can't see anything else.

" OR (((([Forms]![frmreports]![cmbunit]) Is Null) AND (([Forms]![frmreports]![cmbrptactivity]) Is Null) ... "

[Mansish Prasher]

i am checking for three fields
1, if activity is not selected
2, if unit is not selected
3, if only date is selected
this is the third case... the only thing that makes me curious is that this qry works perfectly fine in access but has issues only when run through vba

[June7]

Did you try pulling the control references out of the quote marks and concatenating?

[Mansish Prasher]

Did you try pulling the control references out of the quote marks and concatenating?

Yes i did, but it aint work getting me on my nerves.... i will try to figure out some other workaround but this seems doable, just that i am missing on some syntax or reference i guess....

[June7]

I've never tried to build a query (Access object or VBA) that has that sort of parameter. Apparently the VBA construct is just seeing a string of characters and not treating it as reference to a form control if within quote marks so seems the concatenation should work which should evaluate to True/False.

Maybe:

IsNull([Forms]![frmreports]![cmbunit]) AND IsNull([Forms]![frmreports]![cmbrptactivity])


I build the WHERE clause conditionally. Review http://allenbrowne.com/ser-62code.html