Help: ID number surrounded by symbols

[memaxt]

Hi there,



I have a form where user input their references but unfortunately the reference numbers they have inputted separated via commas, semi-colons, apostrophes

i.e 500, 501, or 501; 502; or "501", "502" or even 500, '502'

is there away if i click the number itself i.e 500 it will take me to the corresponding record? even though its surrounded by symbols?

Many thanks
Max

[ranman256]

I dont see how they are ENTERING data....if its from a form they wouldnt need to put the quotes or commas.

You may have to do some parsing...cleanup before it gets to the query...like: LEFT(txtBox, 3) or the like.

[memaxt]

Hi there,

Its imported data from a website, going forward I wont allow them to use such symbols.

Apologies I'm not quite up to scratch with parsing, are you able to guide me a little more on how i would do this.

Many thanks
Max

[John_G]

It's not as easy as it looks - if the reference numbers are all in one field (which from your description is the case), then when the control is clicked, you have to determine where the mouse cursor is in the field, and then figure out what number is underneath the cursor (if there is one).

The Selstart property will give you the location of the cursor when the control is clicked, but beyond that, you will need VBA to what the number is. Access knows only that the control contains text (characters).

HTH

John

[memaxt]

Hi John,

Many thanks for your response, sounds little confusing to how i would go around this, I've decided to do a Control Find and replace, and remove all symbols so it would leave the textbox with clean reference numbers i.e. Reference Number(s): 500 501 502

being able to click one of those numbers and to go to record seems possible at the moment.

Many thanks
Max

[June7]

Seems possible or impossible?

This is not a normalized data structure. Even a multi-value field might be preferable, and I don't use multi-value fields.

Parsing can be done with string manipulation functions (Left, Right, Mid, InStr, Len). Access Help has more info. Also, VBA code can parse the string to an array. However, any approach used requires data to be consistent structure. All those variations in punctuation would be an issue so it's a positive move that you are dealing with that much.

Suggest a non-bound textbox for user input of parameter and use that as criteria for search.