list for paramterized queries not using InStr

[Guest]

I think from MS Site I got this syntax to allow the user to type in a list of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there other syntax
that allows the user to enter N number of IDs and get only those ids?

tia

 

[Duane Hookom]

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] & ",","," &
[MemberNumber] & ",")

 

[Guest]

Thanks, I'll try it. But:

A: Can you explain it (all the & , )
B. Does it limit the number of IDs the user can enter?

Thanks

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] & ",","," &
[MemberNumber] & ",")



--
Duane Hookom
MS Access MVP
--

I think from MS Site I got this syntax to allow the user to type in a list
of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there other
syntax
that allows the user to enter N number of IDs and get only those ids?

tia

 

[Guest]

That seems to have done it! Thanks. But again, what is all the extra stuff?
(I'm still searching the InStr operator

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] & ",","," &
[MemberNumber] & ",")



--
Duane Hookom
MS Access MVP
--

I think from MS Site I got this syntax to allow the user to type in a list
of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there other
syntax
that allows the user to enter N number of IDs and get only those ids?

tia

 

[Douglas J. Steele]

What Duane's suggesting puts extra commas at the beginning and end of
whatever the user types in, and puts commas around the specific member
number being searched for. The reason for do this is to get rid of "false
positives". For instance, if your user typed in 110,113,142, InStr would
report that 11 is in the string. Using Duane's approach, you'd be looking
for the string ,11, in the string ,110,113,142, so you wouldn't get a hit.

It doesn't limit what the user can type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, I'll try it. But:

A: Can you explain it (all the & , )
B. Does it limit the number of IDs the user can enter?

Thanks

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] & ",",","
&
[MemberNumber] & ",")



--
Duane Hookom
MS Access MVP
--

I think from MS Site I got this syntax to allow the user to type in a
list
of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there other
syntax
that allows the user to enter N number of IDs and get only those ids?

tia

 

[Guest]

Thanks for the answer, but why does putting those commas in eliminate the
"false Positives". I think I understand why the first comma works, but not
the extra ones.....or is the answer just too technical about how the query is
processed.

What Duane's suggesting puts extra commas at the beginning and end of
whatever the user types in, and puts commas around the specific member
number being searched for. The reason for do this is to get rid of "false
positives". For instance, if your user typed in 110,113,142, InStr would
report that 11 is in the string. Using Duane's approach, you'd be looking
for the string ,11, in the string ,110,113,142, so you wouldn't get a hit.

It doesn't limit what the user can type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, I'll try it. But:

A: Can you explain it (all the & , )
B. Does it limit the number of IDs the user can enter?

Thanks

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] & ",",","
&
[MemberNumber] & ",")



--
Duane Hookom
MS Access MVP
--

I think from MS Site I got this syntax to allow the user to type in a
list
of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there other
syntax
that allows the user to enter N number of IDs and get only those ids?

tia

 

[Duane Hookom]

The extra commas would change your user input string to something like:
,10,21,255,329,
Which would be compared to field values like:
,10, match
,25, no match
,29, no match
,329, match

--
Duane Hookom
MS Access MVP
--

Thanks for the answer, but why does putting those commas in eliminate the
"false Positives". I think I understand why the first comma works, but
not
the extra ones.....or is the answer just too technical about how the query
is
processed.

What Duane's suggesting puts extra commas at the beginning and end of
whatever the user types in, and puts commas around the specific member
number being searched for. The reason for do this is to get rid of "false
positives". For instance, if your user typed in 110,113,142, InStr would
report that 11 is in the string. Using Duane's approach, you'd be looking
for the string ,11, in the string ,110,113,142, so you wouldn't get a
hit.

It doesn't limit what the user can type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, I'll try it. But:

A: Can you explain it (all the & , )
B. Does it limit the number of IDs the user can enter?

Thanks

:

Try something like:
InStr("," & [Type Member Numbers separated by commas,Blank=All] &
",",","
&
[MemberNumber] & ",")



--
Duane Hookom
MS Access MVP
--

I think from MS Site I got this syntax to allow the user to type in a
list
of
query parms for a query :

Expr1: InStr([Type Member Numbers separated by
commas,Blank=All],[MemberNumber])

But, because of the InStr i get other members as well. Is there
other
syntax
that allows the user to enter N number of IDs and get only those
ids?

tia