Mutiple Results

[acss]

I am trying to use the Instr statement with no success for my VendorCtry
field. The following is what i have in the query field for InStr([ENTER CTRY
SEPARATED BY COMMA],[VendorCtry]). Is this not used to obtain multiple
parameter entries?

 

[Bob Barrows [MVP]]

I am trying to use the Instr statement with no success for my
VendorCtry field. The following is what i have in the query field for
InStr([ENTER CTRY SEPARATED BY COMMA],[VendorCtry]). Is this not used
to obtain multiple parameter entries?

Sort of. This should work. You will find it easiest to enter this in SQL
View (use the View menu, toolbar button, or right-click menu to switch to
SQL View) rather than in the query builder grid:

WHERE InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])>0

If you have SQLViewphobia, enter this in the top row of the grid (where a
field name would normally go):
InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])

and this goes into the criteria row under it:

 

[acss]

Perfect. I have also tried it on another field within the query and now the
end user can enter a number of parameter entries in either of the two fields.

Thanks

I am trying to use the Instr statement with no success for my
VendorCtry field. The following is what i have in the query field for
InStr([ENTER CTRY SEPARATED BY COMMA],[VendorCtry]). Is this not used
to obtain multiple parameter entries?

Sort of. This should work. You will find it easiest to enter this in SQL
View (use the View menu, toolbar button, or right-click menu to switch to
SQL View) rather than in the query builder grid:

WHERE InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])>0

If you have SQLViewphobia, enter this in the top row of the grid (where a
field name would normally go):
InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])

and this goes into the criteria row under it:

0

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Ken Sheridan]

A couple of additional points:

1. To avoid finding values which are a substring of a value in the comma
delimited list, e.g. finding York when looking for New York, wrap the value
of the column in commas.

2. If, when applying parameters to two columns, you want to make them
optional, i.e. a use can enter values in one, both or none, then test each
parameter for 'OR IS NULL' e.g.

WHERE (INSTR("," & [ENTER CTRY SEPARATED BY COMMA]
& ",", "," & VendorCtry & ",") > 0
OR [ENTER CTRY SEPARATED BY COMMA] IS NULL)
AND (INSTR("," & [ENTER PRODUCT SEPARATED BY COMMA]
& ",", "," & Product & ",") > 0
OR [ENTER PRODUCT SEPARATED BY COMMA] IS NULL);

Note that each OR operation is within parentheses to force it to evaluate
independently of the AND operation.

For a more user-friendly, and typo-proof, approach use multi-select list
boxes on a dialogue form to select the multiple values. For a list box of
countries say, add the list box and then a hidden text box,
txtCtyrListHidden, to the form. Then in the list box's After Update event
procedure put:

Dim varItem As Variant
Dim strCtryList As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' empty hidden text box
txtCtryListHidden = Null

' loop through list box's ItemsSelected collection
' and build comma delimited value list
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCtryList = strCtryList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

Me.txtCtryListHidden = strCtryList & ","
End If

To clear the selections you can add a button with code like this in its
Click event procedure:

Dim n As Integer

For n = 0 To Me.List0.ListCount - 1
Me.List0.Selected(n) = False
Next n

Me.txtCtryListHidden = Null

Create other multi-select list boxes and hidden text boxes on the form in
the same way. Then in the query reference the hidden text boxes as the
parameters, e.g.

WHERE (INSTR("," & Forms!YourFormName!txtCtryListHidden
& ",", "," & VendorCtry & ",") > 0
OR Forms!YourFormName!txtCtryListHidden IS NULL)
AND (INSTR("," & Forms!YourFormName!txtProdListHidden
& ",", "," & Product & ",") > 0
OR Forms!YourFormName!txtProdListHidden IS NULL);

Add a button to the form to open the query, or better still a form or report
based on the query.

One final point; If the values might contain commas use a character such as
the tilde '~' as the delimiter. That's assuming no values will contain a
tilde of course.

Ken Sheridan
Stafford, England

Perfect. I have also tried it on another field within the query and now the
end user can enter a number of parameter entries in either of the two fields.

Thanks

I am trying to use the Instr statement with no success for my
VendorCtry field. The following is what i have in the query field for
InStr([ENTER CTRY SEPARATED BY COMMA],[VendorCtry]). Is this not used
to obtain multiple parameter entries?

Sort of. This should work. You will find it easiest to enter this in SQL
View (use the View menu, toolbar button, or right-click menu to switch to
SQL View) rather than in the query builder grid:

WHERE InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])>0

If you have SQLViewphobia, enter this in the top row of the grid (where a
field name would normally go):
InStr("," & [ENTER CTRY SEPARATED BY COMMA] & ",",[VendorCtry])

and this goes into the criteria row under it:

0

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"