query parameters via multiselect list box

J

johng

I’ve created a form for passing parameters for a report whose record source is a query with no criteria;

as you see, I’m using DoCmd with a ‘where’ portion to pass the criteria parameters –

The date parm is from a text box, the Campaigns are form a multiselect List box with code to create a string and make it the value of another text box ….

Result is: I’m prompted for each parameter, and if I just click OK – no records are retrieved; If I enter the parameters (the ones I’ve put in my ‘Where’) I’m returned ALL records – the ‘where’ clause is ignored ….. ?!?!?!?!

My procedure in the form :

Public Sub cmndCapCampAlpha_Click()
On Error GoTo Err_cmndCapCampAlpha_Click

Dim stDocName As String
Dim strWhere As String

strWhere = " dbo_T_CONTRIBUTION.cont.dt <= #" & Me![AsOfDate] & "#" _
& " AND dbo_T_CONTRIBUTION.campaign_no IN(" & Me!txtCampaigns & ")"

Debug.Print strWhere

stDocName = "rptCapitalCampaigns_alpha"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmndCapCampAlpha_Click:
Exit Sub
Err_cmndCapCampAlpha_Click:
MsgBox Err.Description
Resume Exit_cmndCapCampAlpha_Click
End Sub

The immediate window display after the ‘debug.print’ (above):

dbo_T_CONTRIBUTION.cont.dt <= #7/16/2004# AND dbo_T_CONTRIBUTION.campaign_no IN(377,175)

the above is exactly what I want to have as criteria ………

any suggestions ?!
 
V

Van T. Dinh

I am not sure of "dbo_T_CONTRIBUTION.cont.dt"???

Are you referring to *something* "dt" of the Object "cont" of Table
"dbo_T_CONTRIBUTION"???

--
HTH
Van T. Dinh
MVP (Access)


johng said:
I've created a form for passing parameters for a report whose record
Click to expand...
source is a query with no criteria;
as you see, I'm using DoCmd with a 'where' portion to pass the criteria parameters -

The date parm is from a text box, the Campaigns are form a multiselect
Click to expand...
List box with code to create a string and make it the value of another text
box ..
Result is: I'm prompted for each parameter, and if I just click OK - no
Click to expand...
records are retrieved; If I enter the parameters (the ones I've put in my
'Where') I'm returned ALL records - the 'where' clause is ignored ...
?!?!?!?!
My procedure in the form :

Public Sub cmndCapCampAlpha_Click()
On Error GoTo Err_cmndCapCampAlpha_Click

Dim stDocName As String
Dim strWhere As String

strWhere = " dbo_T_CONTRIBUTION.cont.dt <= #" & Me![AsOfDate] & "#" _
& " AND dbo_T_CONTRIBUTION.campaign_no IN(" & Me!txtCampaigns & ")"

Debug.Print strWhere

stDocName = "rptCapitalCampaigns_alpha"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmndCapCampAlpha_Click:
Exit Sub
Err_cmndCapCampAlpha_Click:
MsgBox Err.Description
Resume Exit_cmndCapCampAlpha_Click
End Sub

The immediate window display after the 'debug.print' (above):

dbo_T_CONTRIBUTION.cont.dt <= #7/16/2004# AND
Click to expand...
dbo_T_CONTRIBUTION.campaign_no IN(377,175)
 
V

Van T. Dinh

* Check the Field references in the WhereCondition and make sure they match
the Report's RecordSource.

* Check that the txtCampaigns are properly separated by the list separators
(comma?).

* Try putting a space between IN and (. I don't think it makes any
difference but I do have a space always.

* If you still can't spot the error, post the RecordSource for the Report
and the Debug.Print the Where String

--
HTH
Van T. Dinh
MVP (Access)


johng said:
Van,
Good catch !! - my typo, that's should be:
dbo_T_Contribution.cont_dt
it's a date field
at any rate, I re-tested after correcting the typo and I still have the
Click to expand...
same problem. While debug.print shows the "correct" where clause,
 
J

johng

The record source is a query with no parameters :

SELECT Sum(dbo_T_CONTRIBUTION.cont_amt) AS SumOfcont_amt, Sum(dbo_T_CONTRIBUTION.recd_amt) AS SumOfrecd_amt, dbo_T_CONTRIBUTION.customer_no, dbo_T_CUSTOMER.lname, dbo_T_CUSTOMER.fname, dbo_TR_CAMPAIGN_CATEGORY.description, dbo_T_CAMPAIGN.description
FROM (((dbo_T_CONTRIBUTION INNER JOIN dbo_T_CAMPAIGN ON dbo_T_CONTRIBUTION.campaign_no = dbo_T_CAMPAIGN.campaign_no) INNER JOIN dbo_T_FUND ON dbo_T_CONTRIBUTION.fund_no = dbo_T_FUND.fund_no) INNER JOIN dbo_T_CUSTOMER ON dbo_T_CONTRIBUTION.customer_no = dbo_T_CUSTOMER.customer_no) INNER JOIN dbo_TR_CAMPAIGN_CATEGORY ON dbo_T_CAMPAIGN.category = dbo_TR_CAMPAIGN_CATEGORY.id
GROUP BY dbo_T_CONTRIBUTION.customer_no, dbo_T_CUSTOMER.lname, dbo_T_CUSTOMER.fname, dbo_TR_CAMPAIGN_CATEGORY.description, dbo_T_CAMPAIGN.description
ORDER BY dbo_T_CONTRIBUTION.customer_no;

the debug where clause is :

dbo_T_CONTRIBUTION.cont_dt <= #7/20/2004# AND dbo_T_CONTRIBUTION.campaign_no IN (175)

The result is that I get promted for the "as of date" parameter as well as the "campaign_no" parameter - and it basically ignore the "IN (175)" criteria and selects all campaign_no's

Note that the "Filter" gets properly updatede with my where clause ?!?!

just can't figure this out ........

Thanks for any help .....



johng said:
I’ve created a form for passing parameters for a report whose record source is a query with no criteria;

as you see, I’m using DoCmd with a ‘where’ portion to pass the criteria parameters –

The date parm is from a text box, the Campaigns are form a multiselect List box with code to create a string and make it the value of another text box ….

Result is: I’m prompted for each parameter, and if I just click OK – no records are retrieved; If I enter the parameters (the ones I’ve put in my ‘Where’) I’m returned ALL records – the ‘where’ clause is ignored ….. ?!?!?!?!

My procedure in the form :

Public Sub cmndCapCampAlpha_Click()
On Error GoTo Err_cmndCapCampAlpha_Click

Dim stDocName As String
Dim strWhere As String

strWhere = " dbo_T_CONTRIBUTION.cont.dt <= #" & Me![AsOfDate] & "#" _
& " AND dbo_T_CONTRIBUTION.campaign_no IN(" & Me!txtCampaigns & ")"

Debug.Print strWhere

stDocName = "rptCapitalCampaigns_alpha"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmndCapCampAlpha_Click:
Exit Sub
Err_cmndCapCampAlpha_Click:
MsgBox Err.Description
Resume Exit_cmndCapCampAlpha_Click
End Sub

The immediate window display after the ‘debug.print’ (above):

dbo_T_CONTRIBUTION.cont.dt <= #7/16/2004# AND dbo_T_CONTRIBUTION.campaign_no IN(377,175)

the above is exactly what I want to have as criteria ………

any suggestions ?!
Click to expand...
 
Top