Joined tables for query, but, not finding field if it starts with zero

[Darlene1409]

OK, I am stumped...I have a table with zip codes and installation cities in it and a table with addresses and zip codes...I joined the zip codes on the two tables and ran a query so the installation city will show up on the address table, but, Access ignores any zip codes that starts with a '0'. I already made sure the zip code field was text. It makes no difference whether I import the tables or link the tables to the original excel sheets. What am I missing???

[RuralGuy]

What does the SQL for your query look like?

[Darlene1409]

OK, I am stumped...I have a table with zip codes and installation cities in it and a table with addresses and zip codes...I joined the zip codes on the two tables and ran a query so the installation city will show up on the address table, but, Access ignores any zip codes that starts with a '0'. I already made sure the zip code field was text. It makes no difference whether I import the tables or link the tables to the original excel sheets. What am I missing???

SELECT [POST Scrub List by Install City].[last name], [POST Scrub List by Install City].[first name], [POST Scrub List by Install City].street, [POST Scrub List by Install City].city, [POST Scrub List by Install City].state, [POST Scrub List by Install City].zip, [POST Scrub List by Install City].phone, [POST Scrub List by Install City].[iss date], [POST Scrub List by Install City].[Install City] INTO NJ
FROM [POST Scrub List by Install City] INNER JOIN [Zip Codes2] ON [POST Scrub List by Install City].zip = [Zip Codes2].Zip
WHERE ((([POST Scrub List by Install City].[Install City])="NJ"));

[RuralGuy]

Try a LEFT JOIN rather than an INNER JOIN and I do not see where you are pulling any fields from the [Zip Codes2] table.

[Darlene1409]

I have this same query for many states, however, the problem seems to be that it will not read the NJ zip codes that start with a '0'....I have imported the zip codes from an excel spreadhseet that lists zip codes for many states, but, it won't read NJ zips that start with '0'....I don't think my join is the problem as it works on all the other states....

[RuralGuy]

I think one of the tables is numeric but try this:

   Dim MySQL As String
   MySQL = "SELECT " & _
           "[POST Scrub List by Install City].[last name], " & _
           "[POST Scrub List by Install City].[first name], " & _
           "[POST Scrub List by Install City].street, " & _
           "[POST Scrub List by Install City].city, " & _
           "[POST Scrub List by Install City].state, " & _
           "[POST Scrub List by Install City].zip, " & _
           "[POST Scrub List by Install City].phone, " & _
           "[POST Scrub List by Install City].[iss date], " & _
           "[POST Scrub List by Install City].[Install City] " & _
           "INTO NJ FROM " & _
           "[POST Scrub List by Install City] " & _
           "INNER JOIN [Zip Codes2] ON " & _
           "(Val([POST Scrub List by Install City].zip) = Val([Zip Codes2].Zip)) " & _
           "WHERE " & _
           "[POST Scrub List by Install City].[Install City]) = 'NJ' ;"

The point I was trying to make earlier is that I can see no reason for the JOIN in this query since you are never using any fields in the [Zip Codes2] table for anything; or am I just missing something.