Left

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rollover99 - 26 May 2006 17:54 GMT

I have searched many threads to try and find an answer. I have a field that
has
many alpanumeric values in it.

Like SO12345 or PO54321

I am trying to get just remove the text from number in a query.
Field1 Field2
SO 12345
PO 54321

I have used this and get the Field1 but I am at a loss for field2

Left(dbo_INVOICE!SALESORDERNUMBER,InStr(dbo_INVOICE!SALESORDERNUMBER,"SO")-1)

Reply to this Message

Rick B - 26 May 2006 18:01 GMT

If the field is always the exact length and format you indicate, then use...

Left([YourField],2)

and

Right([YourField],5)

Signature

Rick B

>I have searched many threads to try and find an answer. I have a field
>that
[quoted text clipped - 11 lines]
>
> Left(dbo_INVOICE!SALESORDERNUMBER,InStr(dbo_INVOICE!SALESORDERNUMBER,"SO")-1)

Reply to this Message

rollover99 - 26 May 2006 18:29 GMT

The left command works but the right gives me the last 2 numbers and the
right may change in length.

So pretty much I have the first 2 letters removed into Field1 but Field2
needs to just show everything else and with using

Right([myfield],5) only returns the last 2 numbers.

Reply to this Message

rollover99 - 26 May 2006 18:32 GMT

Got it.

Thanks for all your help on this.

The number by default has 8 numbers and I just increased the number to
facilitate this.

End result Right([myfield],8)

Reply to this Message

Tom Lake - 26 May 2006 18:24 GMT

>I have searched many threads to try and find an answer. I have a field
>that
[quoted text clipped - 11 lines]
>
> Left(dbo_INVOICE!SALESORDERNUMBER,InStr(dbo_INVOICE!SALESORDERNUMBER,"SO")-1)

Mid(dbo_INVOICE!SALESORDERNUMBER, 3)

Tom Lake

Reply to this Message