How do I clean up table - field Zip Code?

[BennyWong]

Hello All,
I am working on Access 2000. I have a table named tblclient. In this table I have a field named zip which
is for zip code. I have originally defined the datatype
for this field as text. I would now like to redefine the
field as numeric and update the table accordingly. I have
attempted by changing the datatype to numeric but the result has trunicated the last four digit and dash. Here is an example:

Before truncation: 94121-1016
After truncation: 94121

This is what I would like to accomplish.

1. Be able to update the datatype for the field zip from text to numeric without truncation.

What are the steps that I need do to accomplish this?

Thanks in advance for any suggestion or help.

 

[telephoto]

Dont define it as numeric. As a general rule only use number as a datatype when you will be potentially doing calculations with it.

define it as text, check the length of the string allowed in the table design.

If you already have a form set to this table it will be necessary to reset the properties for the field. (Resetting the table properties will not reset the properties of any existing forms using that field)

HTH
Telephoto

 

[FancyPrairie]

Assuming that all of the zip codes follow the format you specified, create a new module and copy and paste the following function into the new module. Then execute it. Note that I'm using transaction handling. Therefore, if there is an error, everything is rolled back and nothing gets updated. That way you can fix the problem and try again. Before running this code, make sure you backup your database (or at least the table) in case you screw up.

Prior to running this, I would create a new zip code field in the table and define it as Long. Now you have two zip code fields; one as text, the other as long. After you run this function, you can delete the text zip code definition from the table.

Function ChangeZipCode()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim wsp As DAO.Workspace

    Dim bolBeginTrans As Boolean

    On Error GoTo ErrHandler

    bolBeginTrans = False

    Set dbs = CurrentDb
    Set wsp = DBEngine.Workspaces(0)

    Set rst = dbs.OpenRecordset("Select OldZip, NewZip From YourTable;")

    wsp.BeginTrans

    With rst
        While Not .EOF
    
            .Edit
            !NewZip = CLng(Mid(!OldZip, 1, 5) & Mid(!OldZip, 7, 4))
            .Update
            .MoveNext
    
        Wend
    End With
    
    wsp.CommitTrans
    bolBeginTrans = False
    msgbox "updated successfully"

ExitProcedure:

    On Error Resume Next

    rst.Close
    Set rst = Nothing

    Exit Function

ErrHandler:

    MsgBox Err.Number & ": " & Err.Description

    If (bolBeginTrans) Then
        wsp.Rollback
        bolBeginTrans = False
    End If
    
    Resume ExitProcedure
    
End Function

 

[FancyPrairie]

Telephoto is right. Don't define it as numeric. Rather keep it as text. However, get rid of the dash. It's not needed. You can insert the dash via output formatting. However, you can still use my code. It strips the dash out.

Instead of adding a new field as long, just use the old field. When the code has finished running, change the size of the field. Here's the same code, but without the extra field (long zip code).

Function ChangeZipCode()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim wsp As DAO.Workspace

Dim bolBeginTrans As Boolean

On Error GoTo ErrHandler

bolBeginTrans = False

Set dbs = CurrentDb
Set wsp = DBEngine.Workspaces(0)

Set rst = dbs.OpenRecordset("Select OldZip From YourTable;&quot

wsp.BeginTrans

With rst
While Not .EOF

.Edit
!OldZip = CLng(Mid(!OldZip, 1, 5) & Mid(!OldZip, 7, 4))
.Update
.MoveNext

Wend
End With

wsp.CommitTrans
bolBeginTrans = False
msgbox "updated successfully"

ExitProcedure:

On Error Resume Next

rst.Close
Set rst = Nothing

Exit Function

ErrHandler:

MsgBox Err.Number & ": " & Err.Description

If (bolBeginTrans) Then
wsp.Rollback
bolBeginTrans = False
End If

Resume ExitProcedure

End Function

 

[BennyWong]

Hello Telephoto and FancyPrairie,
Thank you both for your help and suggestions. I will try both out and see what happens. I really appreciate your quick responses. Again, Thank you very much for your time and efforts.