Instr parsing with a comma delimited string

[Tiana Arylle]

Hi all -

I'm using the InStr function to parse a comma delimited text field for use
in a parameter query. It works like a charm, except that the data in the
field is very small and it's splitting by character.

For example, here's the function I'm using in the query SQL (I snipped the
rest of the SQL for space):
InStr(1,[Forms]![frmAppliesTo].[txtSelections] & ",",[BOOK_ID])

Works great if what's in the text box is:
1
3
1,3

If, however, the text box is "13", it returns records for 1, 3, and 13.

Any hints on how I could fix this? Thanks!

~ Tia

 

[Tiana Arylle]

I forgot to mention that the field I'm searching within is a number field in
a linked table. In the original table, there are leading zeroes, but Access
discards them when it converts to a number type. Unfortunately I can't
change the field type of the linked table.

I tried using a query with a format to add the zeroes back in, but as I need
the output to use in an append query, that didn't seem to work either...

 

[Tiana Arylle]

Thanks for your reply Marshall.

Here's the code I previously snipped, in case it helps. The way I use it is
from a form with a multi-select list box, when the user clicks a button their
selection(s) from the box are passed to a text box. I then call this update
query, which uses the InStr to parse the textbox. I'm far from an expert,
and in fact I found this code here in the forums If there's a better way,
I'm all for it!

UPDATE NEWBOOKS SET NEWBOOKS.LAST_UPDTR_EML_ID = fOSUserName(), NEWBOOKS
..LAST_UPDT_TSTMP = Now(), NEWBOOKS .APLBL_IND = "Y"
WHERE (((NEWBOOKS .BOOK_TYPE_ID)=3) AND ((NEWBOOKS
..CMPGN_ID)=[Forms]![frmUpdateBooks].[cmpgn_id]) AND
((InStr(1,[Forms]![frmAppliesTo].[txtSelections] & ",",[BOOK_ID]))>0));

I'm using the InStr function to parse a comma delimited text field for use
in a parameter query. It works like a charm, except that the data in the
field is very small and it's splitting by character.

For example, here's the function I'm using in the query SQL (I snipped the
rest of the SQL for space):
InStr(1,[Forms]![frmAppliesTo].[txtSelections] & ",",[BOOK_ID])

Works great if what's in the text box is:
1
3
1,3

If, however, the text box is "13", it returns records for 1, 3, and 13.

The problem is somewhere in the stuff you did not post or in
the value of Book_ID.

InStr return the position of the of the matching substring
so you must be doing something with that value. Then you
also have to take the length of the string you are
extracting and the length of the Book_ID substring to get
the starting point of the next item you want to extract.

Have you tried using the Split function instead of a loop
with InStr?

 

[Tiana Arylle]

Mr. Marsh, you are my new hero! Worked perfectly, thanks so much

Here's the code I previously snipped, in case it helps. The way I use it is
from a form with a multi-select list box, when the user clicks a button their
selection(s) from the box are passed to a text box. I then call this update
query, which uses the InStr to parse the textbox. I'm far from an expert,
and in fact I found this code here in the forums If there's a better way,
I'm all for it!

UPDATE NEWBOOKS SET NEWBOOKS.LAST_UPDTR_EML_ID = fOSUserName(), NEWBOOKS
.LAST_UPDT_TSTMP = Now(), NEWBOOKS .APLBL_IND = "Y"
WHERE (((NEWBOOKS .BOOK_TYPE_ID)=3) AND ((NEWBOOKS
.CMPGN_ID)=[Forms]![frmUpdateBooks].[cmpgn_id]) AND
((InStr(1,[Forms]![frmAppliesTo].[txtSelections] & ",",[BOOK_ID]))>0));

Tiana Arylle wrote:
I'm using the InStr function to parse a comma delimited text field for use
in a parameter query. It works like a charm, except that the data in the
field is very small and it's splitting by character.

For example, here's the function I'm using in the query SQL (I snipped the
rest of the SQL for space):
InStr(1,[Forms]![frmAppliesTo].[txtSelections] & ",",[BOOK_ID])

Works great if what's in the text box is:
1
3
1,3

If, however, the text box is "13", it returns records for 1, 3, and 13.

Ahhh, I get it now. The problem is that you are getting
matches that are only part of one of the selected IDs. To
make sure you only get complete matches, you need to also
match the commas at the beginning and end of each ID:

InStr("," & [Forms]![frmAppliesTo].[txtSelections] & ",",
"," & [BOOK_ID] & ",")