Error in Make Table query in Access, but ignoring error table is created

Seems you have one or more rows that doesn't have a name that follows:  letters <space> letters.

No idea how the query works without the INTO.  One or more of the first or last name fields should show an error.

I'm not an Access Expert so there may be a better way but this is what I came up with:

SELECT A1.ProviderID, A1.[Provider Name], Replace(A1.[Provider Last Name],',',' ') AS [Provider Last Name], A1.[Provider First Name], A1.Specialty, A1.NPI INTO Updated_Providers
FROM (
SELECT
	Providers.[ProviderID],
	Providers.[Provider Name],
	IIf(InStr([Provider Name]," ")=0," ", Left([Provider Name],InStr([Provider Name]," "))) AS [Provider Last Name],
	IIf(InStrRev([Provider Name]," ")=0," ", Mid([Provider Name],InStrRev([Provider Name]," "))) AS [Provider First Name],
	IIf(Providers.Specialty Is null, "   ",Providers.Specialty) AS Specialty,
	IIf(Providers.NPI Is null,"    ",Providers.NPI) AS NPI
FROM Providers
) as A1;

Select allOpen in new window