Numeric Error only sometimes

[wtkaufmann]

I queried a linked table with the criteria 'Is Not Null'. The table field I put criteria for has dates listed for each record and I wanted to leave out the records with no date in the field.

After I save the query and run it for the first time, the query works fine. However, when I close the query and open it again the error message "Numeric Field overflow" comes on the screen. When I go back into design view and save the query again then go back to datasheet view, the query works with no problems.

Does anyone have a solution, so that I do not have to open the query in design view, save it, then move into datasheet view again??

 

[JPaulo]

See;

http://support.microsoft.com/kb/815277

If the data is mixed (ie. numeric & text) for a given field, access will pick one data type and assign that to the field. If it decided that the field is numeric, but you have text data, you might get the overflow error the next time to try to append data.

 

[wtkaufmann]

I did take a look into that, and the support page would like me to change the data to a text format, which I would not want to do because the dates are going to used in equations later down the road... however in the field there is one of two things listed, a date in short form (mm/dd/yyyy) or nothing at all.

when there is nothing listed, does access assume its a text and not a date?
any other solutions to the problem in the first post?

 

[Brianwarnock]

I would try looking for an empty field ie =""

Brian

 

[wtkaufmann]

wouldn't that return the results of every field that is blank?

I am looking to retrieve the fields that have dates

 

[Brianwarnock]

No as Blank is " " however I obviously meant <>"" as you are looking to not select empty and null fields therefore Is not null and<>""

This is not tested, I don't know what empty Excel cells transfer as, I just suspect a zero length field rather than a Null.

Brian

 

[wtkaufmann]

Still get an error...

Even tried - LIKE "*/*/????"
Again works when I save the query in Design View and click DataSheet view, However if I close the query and reopen in DataSheet view the error is present.

Any other thoughts???

 

[Brianwarnock]

I can't reproduce the problem.
Creted simple spreadsheet entered dates in some cells not in others, and also deleted dates.
Linke to Access and Is Not Null succeeded in not selecting all rows with no date both from design view and after saving query and then opening it, which I did several times, infact as I typed this I went back clsed the Db , reopened and just selected the query all ok.

Brian

 

[Brianwarnock]

The only other idea that I can come up with is that some of your date cells in Excel are Blank ie the space bar was used. Excel accepts this but a blank in a date field in Access is an error and your table would show #num!, I assume it doesn't else you would have said. This would cause errors of the type mentioned.

Brian

 

[wtkaufmann]

Some of the fields do have the #num!

That is part of my problem... But the excel fields do not have spaces, they are just blank fields surronded by fields wih dates...

Hence why I am trying to query the dated records out of the table with LIKE */*/####. The problem is I can only run the query once right after I save the query in Design Mode...

Thanks for clarifying that... Does anyone have any other advice

 

[Brianwarnock]

Ah so you do have blank as opposed to empty cells, crazily the Excel function IsBlank finds empty cells.
The only solution is to clean up the Excel spreadsheet before linking , a simple function like below will do the trick, obviously you will need to alter the Range

Function correction()
For Each c In Range("b2:b7")
If c.Value = " " Then c.Value = ""
Next c
End Function

Brian

 

[wtkaufmann]

Thanks Brian... Looks like this will work