Parsing in a Query

[OU18]

All,

I have a field Circuit ID, that has varying lengths of data in it. However there are certain portions of the record I am looking to remove from the field.

Circuit ID
2001 T3XF PHLAPAFGK22 PHLAPAAZK03 x19

I need to remove the portion of the data that is x19. The records have an x and then the channel number.

I tried the right(), but this pulled out some information that was needed. There was a circuit ID that had XCN as the last few characters.

What i need to do is look for the (space)X and only remove if the following characters are digits.

Thanks for the help

 

[ByteMyzer]

Try this expression:
[tt]
Left([Circuit ID], InStr(1, [Circuit ID], " x", 0) - 1)
[/tt]

 

[OU18]

Byte,
This works well, but I noticed some anamolies in the data. There are areas where the x19 does not have a space . Is there a way to also remove those where the x is followed by digits.

Thanks
steve

 

[ByteMyzer]

That's ByteMyzer, and all you have to do is change the expression to:
[tt]
Left([Circuit ID], InStr(1, [Circuit ID], "x", 0) - 1)
[/tt]

And it will parse all the text to the left of the "x".

If, however you require the expression to return all text to the RIGHT of the "x", then you should use:
[tt]
Mid([Circuit ID], InStr(1, [Circuit ID], "x", 0) + 1)
[/tt]

 

[OnTheFly]

If you are always looking for the last x in the string you may want to approach it as described above but use the following function instead of the InStr

InstrRev(stringcheck, stringmatch[, start[, compare]])

This function starts looking from Right to Left instead of Left to Right.

Hope this helps.

OnTheFly