Passing Criteria to Report Record Source 1

[dolfin13]

I have an option group on a form that is meant to filter a report. The SQL in the RecordSource refers to a control on this form. I get the report to run fine with the radio buttons that only filter by one criterion, but I can't when I need multiple criteria. For Example, Option1 puts a number 4 in a textbox is referred to in the SQL. Option2 puts "1 AND 4 AND 8" in the textbox. My problem is that Option2 doesn't work. I'm assuming its being treated as a string when the SQL is actually looking for numbers with the AND operator between.

Part of my Select Case:

Case 1 'Option1
Me.txtOwnerFilter = 4
Me.txtClientFilter = 5
Case 6 'Option2
Me.txtOwnerFilter = "1 and 4 and 8"
Me.txtClientFilter = 5



Also, how would I go about having an Option3 that would NOT filter anything?

Case 2 'Option3
Me.txtOwnerFilter = ""
Me.txtClientFilter = 5

I tried multiple ways on this one. I tried to use --Like "*"--, which works in the query builder but not when I tried passing through the text box.

I'm not sure how to go about getting this to work. Though I have a sneaking suspicion that started out in the wrong direction in the first place.

Any help would be greatly appreciated.

Thanks!
Kevin

 

[straybullet]

What is the actual SQL which refers to the textbox? Are you "building" it in sections or is it a straight statement?


Let them hate - so long as they fear... Lucius Accius

 

[dolfin13]

It's a straight statement. Here's the part of the WHERE that references the controls.

((CSP_T_Opportunities.OwnerNew)=[Forms]![F_Prt_ClientStatusRep]![txtOwnerFilter]) AND ((CSP_T_UserNames.UserClient)=[Forms]![F_Prt_ClientStatusRep]![txtClientFilter]))

Thanks for taking a look at this!

 

[straybullet]

One possibility would be to have the text of the WHERE part of the SQL built for each case.

Case 1 'Option1
Me.txtFilter = (CSP_T_Opportunities.OwnerNew=4 AND CSP_T_UserNames.UserClient=5)
Case 2 'Option3
Me.txtFilter = CSP_T_UserNames.UserClient=5
Case 6 'Option2
Me.txtFilter = (CSP_T_Opportunities.OwnerNew=1 OR CSP_T_Opportunities.OwnerNew=4 CSP_T_Opportunities.OwnerNew=8) AND CSP_T_UserNames.UserClient=5


strSQL = SELECT ... WHERE " & Me.txtFilter

Of course, you'll have to experiment with the placement of parentheses and such for each case.

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

straybullet, I'd use this:
Case 6 'Option2
Me.txtFilter = "CSP_T_Opportunities.OwnerNew In (1,4,8) AND CSP_T_UserNames.UserClient=5"

 

[straybullet]

Again with the more efficient ways PHV!
lol ty!

Let them hate - so long as they fear... Lucius Accius