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, 开发者_如何学Pythoneither 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.

---

I suggest

Val(ADDRNO)

It will return the number portion without the leading zeros.

---

I think it's just the order of your parameters that is wrong:

debug.? Replace("My string", "i", "o")   -> "My Strong"

---

You can use Trim and Replace.

---

I'm not sure what context you are running this but this seems to show the parameter order is different and uses double quotes instead of single quotes(I haven't used Access in awhile so maybe it doesn't matter), also try square brackets on column name:

http://www.techonthenet.com/access/functions/string/replace.php

Replace(LTrim(Replace([ADDRNO], "0",  " "))," ", "0")

If that gives the same error just try the replace function by itself to narrow down the problem:

Replace ("alphabet", "a", "e")

If this works then you know the Replace function works, and there is some other issue.

Edit: If it doesn't work at all, then Replace is likely a VBA function available only in the Access application, and is not part of Jet. You could try some combination of Left/Right function and chop the string up, this can get quite ugly. I personally would just iterate over the record set and use C# code to modify the values. Hopefully you don't have such a large number of records that this would be a problem.