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!
