Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    Dallas, Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Type Mismatch with ZIPS in Access tables (Access 2000)

    I am getting a Type Mismatch error when I am building a relationship in a query. I have two tables that I am trying to link by ZIP code. The first table, Table A, is data pulled from multiple SQL tables and converted in a Make-Table query and has these two fields in it:

    ZIP (from SQL database)
    -Contains both 5 and 9 digit ZIPS from SQL table
    Field Type: Text
    Field Size: 10
    Format: (none)
    Mask: (none)

    Ship-to ZIP: (Left(ZIP,5))
    -Contains the first 5 digits of the ZIP field only
    Field Type: Text
    Field Size: 255
    Format: 00000
    Mask: (none)

    Table B was imported from Excel. The numbers in Excel are in standard number format, so to make them have all 5 digits in Excel, I formatted them to have a "Special - ZIP Code" format. When I import it into Access, it goes back to the standard number format and drops all 0's at the beginning of the ZIP codes, so to format it to show all 5 digits, I changed the properties of the cell in the table to look like this:

    Rep ZIP:
    -Contains 5 digit ZIP codes only
    Field Type: Number
    Field Size: Double
    Format: 00000
    Mask: (none)

    If I try to convert Table B "Rep ZIP's" field to text, it says I will lose all the data. If I try to convert Table A's "ZIP" or "Ship-to ZIP" fields to numbers, it also tells me that I am going to lose data.

    My question is: What is the easiest way to format either one of the Table A fields to type match Table B's field, or vice versa? Is there a function that I can use that will convert the ZIP code into 5-digit text? Or is there a way to format the data in Table B while it is still in Excel that will make it import as text but still retain the 5-dight format? I need to be able to match the 5-digit ZIPS, which is why I created the "Ship-To ZIP" field in Table A, because I feared that linking the "ZIP" field in Table A and the "Rep ZIP" field in Table B would not pull any 9-digit ZIP records in Table A when the query was run.

    Thank you so much for your assistance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Type Mismatch with ZIPS in Access tables (Access 2000)

    For example: open Table B in design view.
    Add a new field Zip5, type Text, length 5.
    Close & save the design.

    Create a query based on Table B.
    Add the Zip5 field to the query grid.
    Select Query | Update Query.
    Enter the following expression in Update To: Format([Zip],"00000")
    Select Query | Run and confirm that you want to update the records.

    You should now have a field Zip5 that can be linked to Ship-to ZIp in Table A. (Oh yeah, reduce the size of Ship-to Zip from 255 to 5.)

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Location
    Dallas, Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Type Mismatch with ZIPS in Access tables (Access 2000)

    That worked perfectly, thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •