text function

[ddtpmyra]

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]

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:

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]

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]

The combination of MID and INSTR function works perfectly fine.

Thanks a ton!

[missinglinq]

Glad we could help!

Linq ;0)>