Prev: Return Random Sampling from Query
Next: Oldest and Most Recent dates in header
From: Tiana Arylle on 30 Mar 2010 12:36
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
From: Tiana Arylle on 30 Mar 2010 14:20
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" wrote:

> 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
From: Marshall Barton on 30 Mar 2010 14:23
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.


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?

--
Marsh
MVP [MS Access]
From: Tiana Arylle on 30 Mar 2010 16:48
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));


"Marshall Barton" wrote:

> 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.
>
>
> 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?
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Marshall Barton on 30 Mar 2010 18:38
Tiana Arylle wrote:
>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));
>
>
>"Marshall Barton" wrote:
>
>> 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] & ",")

--
Marsh
MVP [MS Access]
 |  Next  |  Last
Pages: 1 2
Prev: Return Random Sampling from Query
Next: Oldest and Most Recent dates in header