Prev: Return Random Sampling from Query
Next: Oldest and Most Recent dates in header
From: Tiana Arylle on 31 Mar 2010 10:44
Mr. Marsh, you are my new hero! Worked perfectly, thanks so much :) :)

"Marshall Barton" wrote:

> 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]
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Return Random Sampling from Query
Next: Oldest and Most Recent dates in header