SetParameter to Range or multiple Value

[tobypsl]

Hi

I have the code below on a button on a report:

Private Sub Command53_Click()
DoCmd.OpenForm "frmWaitMessage"
DoCmd.SetParameter "Letter-Received", True Or False
DoCmd.SetParameter "1A-Received", True
DoCmd.SetParameter "Statuses", 1
DoCmd.SetParameter "Qty-Identified", 2
DoCmd.OpenQuery "qryCall_Activity_Viewer", , acReadOnly
DoCmd.Close acForm, "frmWaitMessage", acSaveYes
DoCmd.SelectObject acQuery, "qryCall_Activity_Viewer", False
End Sub

And this works fine, but if I want to set the parameter "Statuses" to a range I tried:

• >0
• Between 1 and 3
• 1 Or 2 Or 3

But they all throw an error.

Is it possible to set parameters to ranges or multi values or just a single string?

Thanks.

 

[tobypsl]

OK, so I've come up with a work around. In the underlying Query, I prefix the values in the field with the [Statuses] parameter with a T - so T1, T2 or T3 rather than 1, 2 or 3.

Then in the query, rather than just the parameter, I have

Like "[Statuses]*"

and then for the range I can use

Docmd.SetParameter "Statuses", T

and it pulls up all values.

This works for this particular field - because I either want to search 1 value or any rather than 2 of 3 etc., but for more complex scenarios would not do. So if someone can tell me whether there is a method for determining ranges or multi-values using the SetParameter method that would be great.

ACTUALLY THE ABOVE STILL DOESN'T WORK.

 

[NauticalGent]

To answer your question, yes it is possible.

What error is being given?

 

[tobypsl]

>0 throws a syntax error
Between 1 and 3 gives Compile Error: Expected end of statement
1 Or 2 Or 3 gives items with the last value only (ie. 3 or for 1 or 2 it would give items with value of 2 only)

 

[NauticalGent]

Hopefully some of the more savvy members will see my fumbling around and weigh in here. But since I started, I may was well continue!

I THINK the issue is you are using numbers in your parameters while Statuses is a text field? Although in the query grid it works, setting them with VBA may irritate the Access Gnomes (copyright infringement). Try using quotes and see if that fixes it. For example "1" or "2" or "3".

Also, I can't help but ask why would you "hard code" your parameters when simply putting them in a stored query would achieve the same results?

 

[tobypsl]

I tried that already but same result.

I'm hard coding them because the report contains number generated from a query with a variety of WHERE clauses representing various different scenarios. The command button (with the setparameter) opens the query with the parameters set to display results corresponding to the specific scenarios. So, for example, if the report has 5 scenarios, each with a number representing a record count for that scenario, the command button set's the parameters to pull those records thorugh the query. I could just set up a separate query for each scenario but thought this method would be easier.

 

[NauticalGent]

I see...

You may have tried this already, but have you tried using those ranges in a conventional query to see if it returns the results you are looking for?

 

[tobypsl]

Yes I have tested using a range and multiple values and it delivers as expected. It's just when I try to set parameters it doesn't work.

 

[NauticalGent]

Alright then. Have you viewed the query (the one that works) in SQL mode - it may show you the proper syntax that differs from what you have tried.