It looks right. When I have such a problem, I deal with it by simplifying the
query until it works then build back up from there.
I'd first replace the SELECT DISTINCTROW with SELECT DISTINCT. Maybe
something strange is happening there due to the table being linked to Excel.
If that didn't work, I'd next take out the DISTINCT.
After that I'd take out the ORDER BY clause. Probably not the problem though.
If still not working, I'd take out the WHERE clause a little at a time until
it works OR take it all the way out at first then build it back up until
failure.
If something like below doesn't work, then there's possibly a corruption
problem with the Clean Branch List table OR maybe some field constraint like
no nulls or no zero length strings in that table.
SELECT [Link Here].[Cost Center],
[Link Here].[Branch Name],
[Link Here].Region,
[Link Here].Market,
[Link Here].Address,
[Link Here].City,
[Link Here].State,
[Link Here].Zip
INTO [Clean Branch List]
FROM [Link Here] ;
One other thought: You may need to precreate the table with the proper data
types. Access often looks at the first few rows of the Excel worksheet and
tries to determine the field type form it. Access might find a bunch of small
numbers in a field and make it an Integer. Then a few rows down you have a
number like 123,456,789.01 which won't fit into an Integer field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
WildlyHarry said:
SELECT DISTINCTROW [Link Here].[Cost Center], [Link Here].[Branch Name],
[Link Here].Region, [Link Here].Market, [Link Here].Address, [Link
Here].City, [Link Here].State, [Link Here].Zip INTO [Clean Branch List]
FROM [Link Here]
WHERE ((Not ([Link Here].[Cost Center]) Is Null) AND (([Link Here].[Branch
Name])<>"test" And ([Link Here].[Branch Name])<>"iup") AND (Not ([Link
Here].Region) Is Null) AND (([Link Here].Market)<>"FLA" And ([Link
Here].Market)<>"z test" And ([Link Here].Market)<>"z closed branches"))
ORDER BY [Link Here].Market;
Hope this helps. I cannot figure out why it will work in datasheet view but
it cannot create a table. It has worked before.
Jerry Whittle said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I am using a make table query to gather some information from a table linked
to excel. The query will work fine in design and datasheet view. However
when I try to run the make table function, I get a "Numberic Filed Overflow"
error. How can this query work in datasheet view but not be able to execute
the make table function? Any suggestions?
