Passing a List through a Parameter

[rft]

I would like to pass a list through a parameter, to allow a user to specifiy
which records to retrieve.

The following query returns 4 rows:

select *
from branch
where branchno in ('1','9','21',34');

Add a parameter:

select *
from branch
where branchno in ([p_branchno]);

--branchno is a text field

when executed, prompted for:

Enter paremater value, p_branchno: '1','9','21',34'
no row returned

execute again

Enter paremater value, p_branchno: 1 --(without single quotes)
1 row returned

thanks in advance!

 

[Stefan Hoffmann]

hi,

I would like to pass a list through a parameter, to allow a user to specifiy
which records to retrieve.

How do you build this list?


mfG
--> stefan <--

 

[rft]

I would like to build it by prompting the user executing the query. The user
will decide which branches to retrieve. They can request 1 or more than 1
branches. If needed, I will provide guidance as to the format of entry into
the parameter.

e.g. you must enter the parameter with each branchno enclosed in single
quotes, separated by a comma, etc...

 

[Stefan Hoffmann]

hi,

e.g. you must enter the parameter with each branchno enclosed in single
quotes, separated by a comma, etc...

This is extremly error prone.

Let the user enter the values separated by a comma, then you may use
this function (place into a standard module):

Public Function ValueInString(AValue As Variant, AString As String) _
As Boolean

Dim a() As String
Dim Count As Long
Dim Result As Boolean

Result = False
If Not IsNull(AValue) Then
a() = Split(AValue)
For Count = 0 To UBound(a())
Result = Trim(Str(AValue)) = Trim(a(Count))
If Result Then
Exit For
End If
Next Count
End If
ValueInString = Result

End Sub

and use it in your query as

WHERE ValueInString([yourField], [ENTER VALUES])

This is untested air code, but it should work.


mfG
--> stefan <--

 

[Tom van Stiphout]

I haven't had much luck with that. You would think an IN clause would
work:
select * from myTable
where BranchID in [Gimme the branches:];
(user enters 1,2,3)

But that does not work. You can dynamically create the sql statement
and that *will* work.

-Tom.
Microsoft Access MVP

 

[rft]

I agree. I can do exactly that in Oracle SQL, with number or varchar2 fields.

 

[Marshall Barton]

I would like to pass a list through a parameter, to allow a user to specifiy
which records to retrieve.

The following query returns 4 rows:

select *
from branch
where branchno in ('1','9','21',34');

Add a parameter:

select *
from branch
where branchno in ([p_branchno]);

--branchno is a text field

when executed, prompted for:

Enter paremater value, p_branchno: '1','9','21',34'
no row returned

execute again

Enter paremater value, p_branchno: 1 --(without single quotes)
1 row returned

The problem is that a parameter can not contain query syntax
(i.e. the commas). Parameters can only be a single value,
in your case the string "1,9,21,34"

To get what you want, you need to use an expression that can
work with that single string value. Here's an example of
one way:

WHERE "," & Replace([Enter list], " ", "") & "," Like "*," &
branchno & ",*"

 

[Tom van Stiphout]

On Fri, 30 Jan 2009 09:27:11 -0600, Marshall Barton

Creative!

-Tom.
Microsoft Access MVP

I would like to pass a list through a parameter, to allow a user to specifiy
which records to retrieve.

The following query returns 4 rows:

select *
from branch
where branchno in ('1','9','21',34');

Add a parameter:

select *
from branch
where branchno in ([p_branchno]);

--branchno is a text field

when executed, prompted for:

Enter paremater value, p_branchno: '1','9','21',34'
no row returned

execute again

Enter paremater value, p_branchno: 1 --(without single quotes)
1 row returned

The problem is that a parameter can not contain query syntax
(i.e. the commas). Parameters can only be a single value,
in your case the string "1,9,21,34"

To get what you want, you need to use an expression that can
work with that single string value. Here's an example of
one way:

WHERE "," & Replace([Enter list], " ", "") & "," Like "*," &
branchno & ",*"