How do you strip off trailing characters using Microsoft Access

[Ken Jones]

With 2 separate character strings of
100038_ko and 244p_po how do I strip off the _ko and _po from the respective strings?

[gnawoncents]

You can use a replace function, or simply a length and left combined (assuming it is always a given number of characters to remove) e.g.

myString = Left(myString, Len(myString) - 3)
 

[Ken Jones]

In addition to xxxxxx_po and xxx_fr I have other names in the list like xxxxxxxx without the extension. Would the replace function work better since what you sent me would remove the last 3 characters whether the extensions (ie)_po, _fr, existed or not. Of course I would not want to delete the last 3 characters unless the _xx actually existed.

[Mariostg]

x = "xxxxxx_po"
If InStr(x, "_") Then
    x = Left(x, InStr(x, "_") - 1)
End If
 

Only strips if _ is in the string.

[Ken Jones]

Can I get a free upgrade for my Micrsoft Access to run IF statements? IF not for free, what should I use?

[gnawoncents]

Can I get a free upgrade for my Micrsoft Access to run IF statements? IF not for free, what should I use?

Ken, I'm not sure what you're asking....

Regarding using replace vs. an If statement, it all depends on the strings in question. If there is ALWAYS an underscore in the data to be removed (_) and you ALWAYS want to get rid of it and any trailing characters, Mariostg's suggestion should work great.

If there is not always an underscore, but there are only a handful of different trailing characters, either an If statement or Replace would work fine.

[Stewart Ross]

Please note that the IF statement mentioned above is a VBA statement that would be used in a custom function in a VBA code module.

To use an IF in a query without calling a VBA function you need the in-line version called IIF:

TrimmedField: IIF(InStr([YourFieldName], "_") > 0, Left([YourFieldName], InStr([YourFieldName], "_") - 1), [YourFieldName])

-Stewart