excel Between[]and[] query only works if not saved??

[weeblebiker]

I have a simple query to pull records in a range of serial numbers from a linked excel sheet.
Both the access and the excel files are on a shared network server
It works until I save it, then I get a numeric overflow error. once I open the query and change anything (and before I save it) it works again untill I hit the save icon.

This is frustrating!

maybe the easiest thing would be to copy the excel sheet in access and dump excel?
the major benefit of the excel sheet is I can run graphs, will access graph like excel?

 

[Trevor G]

If your database is saved as the true version then the Access Graphs are fairly good. It is when you use graphs in a database to a lower version I find you get some issues about updating.

So if you have Office 2007 then save the database as 2007 version.

 

[weeblebiker]

I'm running office 2003

 

[Trevor G]

I'm running office 2003

Then I would ensure the database is 2003 and not saved as 2000 to 2002, your graphs should work OK then.

 

[boblarson]

What is the SQL to the QUERY?

 

[weeblebiker]

what's a SQL?
sorry I'm ignorant about access still.
I just converted from 2000 format to 2003. I'll see if that takes car of the numeric overflow problem.

 

[boblarson]

what's a SQL?
sorry I'm ignorant about access still.
I just converted from 2000 format to 2003. I'll see if that takes car of the numeric overflow problem.

You go into your query in design view and then to VIEW > SQL VIEW and then copy the SQL Statement that shows there.

 

[weeblebiker]

SELECT [XRS-3 new unit Test Log].[serial#], [XRS-3 new unit Test Log].B, [XRS-3 new unit Test Log].C, [XRS-3 new unit Test Log].[D ], [XRS-3 new unit Test Log].E, [XRS-3 new unit Test Log].F, [XRS-3 new unit Test Log].Date, [XRS-3 new unit Test Log].[tested by]
FROM [XRS-3 new unit Test Log]
WHERE ((([XRS-3 new unit Test Log].[serial#]) Between [start #] And [End#]));

 

[weeblebiker]

the issue is has not changed

 

[weeblebiker]

I do not get the numeric overflow error if I import the excel table instead of linking to it.

 

[boblarson]

I do not get the numeric overflow error if I import the excel table instead of linking to it.

Sounds like there may be some "hidden" stuff in the Excel sheet causing you problems.

 

[Yarp]

Is your table an .csv file? I have had this when there is an incorrectly defined link to the excel file. I suspect you have text in your file and when linked, Access was not identifing when the text started and ended, resulting in a non-numeric value existing in an numeric column within Access.

Make any sense?

 

[weeblebiker]

no text, cell format set to number in excel, I have empty cells in the column,,,,,,,,is this the cause?

again, the query works until I save it, only after I save it does it give the numeric overflow error. if it was something with the excel firle why woould the query be able to work at all?

 

[weeblebiker]

I'm gunna shut down and reboot, maybe I've been screwing around in the db too much this morning

 

[weeblebiker]

rebooted, deleted the query deleted the excel link, looked over the excel file, re-linked to access 2002-2003, re-wrote the query

still no workie after I save, just before I save

 

[weeblebiker]

so the query works fine on another sheet in the same excel file.
WTF!?!?!?!
went through and deleted all the conditional formating in the excel sheet, that didn't change the query behavior.

 

[Yarp]

Do you get an error message? Which line errors? Perhaps if you find the line that errors you (we) can identify why?

 

[weeblebiker]

the error is "Numeric Field Overflow"
the table field is a numeric serial#, excel column cell format is number, no decimel
again this error occurs only after the query is saved, it works unsaved

 

[weeblebiker]

GOT IT!!!!!!!!
I scanned the entier excel column for the Third time
a 0 was entered o 2 years ago

so any letter in a number column will only keep the code from working once it is saved, not before. I assumed that since the code worked before saving it could not be a leter in a number column issue.