function to return a portion of a cell

[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!

 

[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

 

[Clifford Bass]

Hi Carrie,

You need to use three functions together. Try:

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

Clifford Bass

 

[carrie]

Perfect - thanks!

Hi Carrie,

You need to use three functions together. Try:

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

Clifford Bass

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!

 

[Clifford Bass]

Hi Carrie,

You are welcome.

Clifford Bass