text function

Question posted by: ddtpmyra (Member) on July 1st, 2008 05:38 PM

Im try to catch the part numbers on a specifict field inside access query and I dont know what kind of function to use.

example data:
new order #ABC-001

the result i wanted is to get all characters that starting with # and end on the 10th character after it for example.

please help.

nico5038

Moderator

2,076 Posts

July 1st, 2008
08:03 PM
#2

Re: text function

For this the string functions can be used in a query.
Check the Instr() function in the help file. It will return the start position of a given character (or string).
When > 0 the sting is found at that position and a Mid() function can be used to extract the needed string.

Sample with the Left() function:

Expand|Select|Wrap|Line Numbers

select left(fieldname,5) from tblX

This will return from your string "new order #ABC-001"
"new o"

Let me know when you run into trouble :-)

Nic;o)

missinglinq

Moderator

2,455 Posts

July 1st, 2008
11:10 PM
#3

Re: text function

Am I correct in understanding that you want to parse out the # sign plus the next 10 characters? If so, something like this should work:

Mid(TextField, Instr(TextField, "#"),11)

With "new order #ABC-00123456789X"

this will return

#ABC-001234

Linq ;0)>

ddtpmyra

Member

71 Posts

July 3rd, 2008
05:10 PM
#4

Re: text function

The combination of MID and INSTR function works perfectly fine.

Thanks a ton!

missinglinq

Moderator

2,455 Posts

July 3rd, 2008
07:03 PM
#5

Re: text function

Glad we could help!

Linq ;0)>

Not the answer you were looking for? Post your question . . .
189,890 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).

Latest Articles: Read & Comment

Top Microsoft Access / VBA Forum Contributors