function to return a portion of a cell

C

carrie

I have a field that I need to trim off the beginning of the text. The piece
to be trimmed is not always the same length but is always followed by a dash.
Please see below:
Currently the field is this: ABC-1001077092
I need it to be: 1001077092
Is there a function in access to do this?
Thanks!
 
J

Jeff Boyce

Take a look at the Mid() and Instr() functions. You can use them in a query
to tell Access to find out where the dash is:

Instr([YourField],"-")

then take all the characters that follow:

Mid([YourField],Instr([YourField],"-")+1)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
C

Clifford Bass

Hi Carrie,

You need to use three functions together. Try:

Right$([YourField],Len([YourField])-InStr(1,[YourField],"-"))

Clifford Bass
 
C

carrie

Perfect - thanks!

Clifford Bass said:
Hi Carrie,

You need to use three functions together. Try:

Right$([YourField],Len([YourField])-InStr(1,[YourField],"-"))

Clifford Bass

carrie said:
I have a field that I need to trim off the beginning of the text. The piece
to be trimmed is not always the same length but is always followed by a dash.
Please see below:
Currently the field is this: ABC-1001077092
I need it to be: 1001077092
Is there a function in access to do this?
Thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Portion of a Text String 4
TRIM function isn't working in my query 3
TRIM function with fixed length field 5
Excel Subtract text after the third space and before the fourth (from between spaces) 3
Extract portion of field 2
Filter Records with a space at the beginning 3
Need help with IIf and Trim$ function in a query 6
Select records based on isolated portion of string 1

Top