Removing leading zeros

[Zuzia]

Hi,

Does anyone know the best way to remove a varying number of leading zeros from a table or query in MS Access 2000? These zeros occur at the beginning of a Address field's street number, and are followed by the number, and then by the street name.

Thanks,
Z

 

[dhookom]

I would use a small user defined function like:

Function RemoveLeadingChars(pstrText As String, _
        pstrChar As String) As String
    Dim intI As Integer
    intI = 1
    Do Until Mid(pstrText, intI, 1) <> pstrChar
        intI = intI + 1
    Loop
    RemoveLeadingChars = Mid(pstrText, intI)
End Function

You can call this function in a query or control source or code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bolens79]

Hi Dhookom

I'm trying to use your code here in an update query and keep getting the error undefined function 'removeleadingchars' in expression. Any ideas will be helpful

thanks

 

[dhookom]

What have you done to implement the code? Did you paste the code into a standard module? Did you name the module something different from the function name?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

Make sure it is a public function so it can be seen everywhere in the application, i.e.

Public Function RemoveLeadingChars....