Formatting Addresses in Large Database (Picture)

[samanthaM]

I have a very large database of Addresses, and I need to format them correctly for a mail merge to be mailed at a later date.
The problem is the city inputs the address with 0 place holders.

The address can contains up to 6 zero value place holders for the house number. 000000

For Example: The address 601 Greenhill will read "000601 Greenhill" in the database. I need to remove any consecutive 0 place holder values from the address beginning of the address.



007701 becomes 7701 (2 consecutive zeros removed)
000608 becomes 608 (3 consecutive zeros removed)
012198 becomes 12198 (1 zero removed)

But its very important to note, it can only remove consecutive zeros from the beginning of the address.
For example:
068001 cannot remove the 0's in red, only the zero at the beginning of the address. Because the 00 in 68001 is part of the address.

See Example from Database below:



Please let me know if you require any clarification, or if you would like an example of the database.

[RuralGuy]

I take it everything is in one text field, correct? What would you expect that last example on Anderson Blvd to be?

[samanthaM]

@RuralGuy,

Yes everything is one text field. Examples such as Anderson Blvd are errors from the city employee who entered the information. In such cases, you have to manually look up the owner in the tax rolls to get the property address.

[orange]

Are you part of the "city" organization? I ask because it seems someone has inputted data and not used a current Tax list of some sort. I would be requesting some sort of read only file derived directly from the Tax List.

If you are not part of that organization, can you or your organization request a digital file based directly on the Tax list?

You should not have to, nor should you, edit records on the Tax list.

Perhaps more info is required, and I have misunderstood.

[RuralGuy]

Here's a Function for a Standard Module that should help you:

Public Function StripLeadZeros(InValue As String) As String
   Dim MyStart As Integer
   Dim Looping As Boolean
   MyStart = 1
   Looping = True
   Do While Looping
      If Mid(InValue, MyStart, 1) = "0" Then
         MyStart = MyStart + 1
      Else
         Looping = False
      End If
   Loop
   StripLeadZeros = Mid(InValue, MyStart)
End Function

[samanthaM]

Here's a Function for a Standard Module that should help you:

Pardon my ignorance, but this is my first attempt to use a function module in access. I googled around, but I am not having any luck. I tried to use your code by create ->module and pasting the code. I then debugged it to make sure it would work and it produced an error.

What am I doing wrong? Also once the module is working correctly, how do I use it specifically on the property address column?


@Orange, No I am not part of the city. This is the only file output you can obtain from the city.

[RuralGuy]

My bad. The code did not past as it should. I've changed it and highlighted the change in RED.

[samanthaM]

My bad. The code did not past as it should. I've changed it and highlighted the change in RED.

It went through debug no problems, thanks. Now that I saved this as a module, how do I run it on the "situs_address" column specifically to remove the leading zeros? Thank you very much with your help and patience.

[RuralGuy]

If you want to make this change permanent then you could do an Update query and Set [situs_address] = StripLeadZeros([situs_address]). If just temporary then create a select query for all of the fields you need but add StripLeadZeros([situs_address]) As YourAddress and don't display the actual [situs_address] field.

[samanthaM]

It appears to be working just fine. Thank you very much for your time and effort RuralGuy, this will help me very much in my day to day business needs.

Thank you once again.

[RuralGuy]

Great! Glad we could help.