Search code examples
sqlms-accessado.netjet

Getting around the lack of a Left Trim(string, char[]) function in JET / Access

I need to remove leading zeros from a string field in an Access database that is destroyed and recreated every time it is used within a C# program. Most string libraries (even SQL ones) include a Trim function to remove leading or following whitespace. Unfortunately, Access does not seem to have a LTrim(string s, char[] trimChars) or something similar. To get around this, I concocted this monstrosity:

Replace(LTrim(Replace(ADDRNO,'0',  ' ')),' ',  '0')

But this resulted in an undefined function reference for Replace, even though it is obviously an Access function.

What I am looking for is a way to trim these zeros, either by getting the JET engine to let me use the Replace function or by some other method entirely.

EDIT: Fixed syntax of Replace function. Problem still persists.

Solution

  • I suggest 

    Val(ADDRNO)

    It will return the number portion without the leading zeros.