pass range as parameter value.

[dodyryda]

Hi ..

maybe someone can help. I need to pass a range of numbers (0 and 1) as a parameter to a query but I can't seem to get it to work.

If I pass just a single number 0 or 1 the query returns the expected result. but If I assign the parameter a range using the vba below the query ignores the second number. If I use quotes around the numbers the query seems to ignore the parameter altogether.

If [CT].Value = "I need this range" Then range.Value = "0,1"

then in my reports input parameters I reference the form
@CaseType int=Forms![RPT Pro-Search]!range

in my query criteria I have IN (@CaseType) against the field I need to look at.

can anyone suggest a possible solution? Many Thanks

 

[VilaRestal]

Why not just inject the range into the SQL:

"IN (" & Forms![RPT Pro-Search]!range & ")"

rather than trying parameterize it?

(And I don't mean "don't use a parameterized query" I just mean don't parameterize that bit of it)

However, if it can work the way you were doing it, it's not working because of @CaseType int.
range is a string not an int.

The more I think about it the more I'm sure you can't do that.

I think you'd have to declare each value in the range as a parameter:

"IN (@CaseType0, @CaseType1, @CaseType2)"

And assign each one to the appropriate element of Split(Forms![RPT Pro-Search]!range,","). Nightmare! Just inject the range as a string it into the SQL.

 

[spikepl]

You cannot pass SQL language elements as parameters, only VALUES. So for a "range" with a fixed number of items you can pass each value as VilaRestal suggests, or do the other thing , and rewrite the SQL in a QueryDef.

 

[dodyryda]

thanks chaps, tried it as a string but it doesn't work either .Thanks for the suggestion Vilarestal I'll give it a go..

 

[dodyryda]

I just tried to use your code to inject the range as suggested but when I go to save the query I get an ADO error: Invalid Column name ' & Forms![RPT Pro-Search]!range & '

 

[VilaRestal]

Save the query?
I meant to do that in VBA at runtime.
Of course that sort of thing means nothing to the query designer.

You'll be setting the recordsource of the report to a SQL string you're building at runtime. Not trying to use a fixed saved query.

 

[dodyryda]

Ah ok thanks , I only had 2 numbers in this range so got it working with 2 parameters as you mentioned but I'll try using the SQL as record source.

 

[gemma-the-husky]

if it is a true range, you can do it as between


if somefield between [startvalue] and [endvalue]

that will work, surely