Left function

[JeffreyDavid]

I have a report that gives me a field named 'Dtl_Reference', and at the begining of the field data, there is 3 - 5 characters in brackets, but I need to sort the report on the 6 characters after the 3 - 5 characters in brackets.
I tried the Left function but with the variable number of characters in brakets, I couldn't get it to work.
Anyone know how to pull data from the middle of a field or eliminate a variable number of charcters from the begining of a field?

 

[asnyder]

Create a field in a query that uses the Right function to exact all the data from the field except the characters on the left that you don't want. Base the report on this query and use this field for sorting.

 

[JeffreyDavid]

Create a field in a query that uses the Right function to exact all the data from the field except the characters on the left that you don't want. Base the report on this query and use this field for sorting.

But what if the number of characters on the left that I want eliminate from the field varies between 3 - 5, but is in brackets?

 

[missinglinq]

The variable number of digits on the left side of the field doesn't really matter if you use the Right function. If the 6 digits that appear after the bracketed numbers are the final digits of the field, asnyder's advice will work.

The Missinglinq

 

[raskew]

Try the mid() function combined with the InStr() function

Say myField="[ab23492]xyz" then

? mid(myfield, instr(myField, "]")+ 1)

will return everthing to the right of the closing bracket.

Try it in the debug window:

myfield="[ab23492]xyz" 

? mid(myfield, instr(myField, "]")+ 1)

xyz

The length of the bracketed statement makes no difference.

What's important is the position of the right bracket.

HTH- Bob

 

[JeffreyDavid]

Try the mid() function combined with the InStr() function

Say myField="[ab23492]xyz" then

? mid(myfield, instr(myField, "]")+ 1)

will return everthing to the right of the closing bracket.

Try it in the debug window:

myfield="[ab23492]xyz" 

? mid(myfield, instr(myField, "]")+ 1)

xyz

The length of the bracketed statement makes no difference.

What's important is the position of the right bracket.

HTH- Bob

That works awesome! I knew there had to be some way to eliminate the text in brackets!
Thanks!