Parameter in a report with a query as a recordsource

[Savvoulidis Iordanis]

I have a report based on a query object.
In the query's criteria I use a function I created, GetParam(x)
where x, is the parameter's array index, from a global parameters array
e.g. for a query object that is based on 'select * from customer where
id=GetParam(0)',
The 0 index in the parameter values array, has the id value of the customer
I want to retrieve.

(by the way, this is the best way I could invent, to pass a parameter value
for reports,
as I could not somehow pass any value for a parameter defined in the query
using the Query menu.
If anyone has done this, I 'd be glad to know how he/she did it)

Now, the problem I have, is how to pass a value for use in an IN clause.
If the column is of string type and in the criteria, I use : IN
(getparam(0))
where getparam(0) returns: "AX1" , "AX2" , "GB1" , "GB2" as one string
value,
the report displays no rows as the query returns nothing, and I can't figure
out why.

I also changed the parameter's value to "AX1" ; "AX2" ; "GB1" ; "GB2" as one
string value, but no luck.

Also, how can I display the sql executed by a report when it is based on a
query as described above?

Could anybody help me here?

TIA
Savvoulidis Iordanis
Greece

 

[Marshall Barton]

I have a report based on a query object.
In the query's criteria I use a function I created, GetParam(x)
where x, is the parameter's array index, from a global parameters array
e.g. for a query object that is based on 'select * from customer where
id=GetParam(0)',
The 0 index in the parameter values array, has the id value of the customer
I want to retrieve.

(by the way, this is the best way I could invent, to pass a parameter value
for reports,
as I could not somehow pass any value for a parameter defined in the query
using the Query menu.
If anyone has done this, I 'd be glad to know how he/she did it)

Now, the problem I have, is how to pass a value for use in an IN clause.
If the column is of string type and in the criteria, I use : IN
(getparam(0))
where getparam(0) returns: "AX1" , "AX2" , "GB1" , "GB2" as one string
value,
the report displays no rows as the query returns nothing, and I can't figure
out why.

I also changed the parameter's value to "AX1" ; "AX2" ; "GB1" ; "GB2" as one
string value, but no luck.

Also, how can I display the sql executed by a report when it is based on a
query as described above?

You can not use a parameter for multiple values, it can only
have a single value.

However, you may be able to use a different approach.

If the Where clause that doesn't work looks like:
[field] IN (getparam(0))
then try using:
InStr("," & getparam(0) & ",", "," & [field] & ",") > 0
or, if there's no chance that the field may have just a part
(e.g. "AX") of the codes returned by the function:
InStr(getparam(0), [field]) > 0

 

[Graham Mandeno]

Hi Savvoulidis

This doesn't work because your GetParam function is returning a single
string, not a list of strings.

I can think of two possible workarounds:

One which would involve minimal changes is to write a function IsInList,
which takes two arguments and tests if the first one is in the list
contained in the second, returning true or false. Then, instead of:
where ( [Field] in GetParam(3) )
you would need to say:
where IsInList( [Field], GetParam(3) )

The other option is to leave your query without a WHERE clause and write a
function to return a filter string for the report. Then you can set the
filter in your Report_Open procedure:
Me.Filter = GetFilterString( Me.Name )
Me.FilterOn = Len(Me.Filter) > 0