Numeric Value out of Range

[pauldooley]

Hi All,
I’m getting an error message on Access that I cant understand. I have 2 databases & 1 Query. All of which are working fine. My error comes when I add a basic formula and try to link the Query to a pivot. If I remove the formula the query can link to the pivot and works perfectly. The formula seems to work perfectly well within access.
I’m getting the following error “[Microsoft][ODBC Microsoft Access Driver]Numeric Value out of Range”
This is driving me nuts

Thanks

Paul

 

[Micron]

I can't tell where your formula is but it sounds like it's in Excel. My guess is that the Excel data type that it returns is not compatible with how it has been defined in Access; i.e. one is text and the other is number, or one is integer and the other is larger, such as single. If the former, I'd expect a data type mis-match error and not that it was out of range.

 

[pauldooley]

The formula is in access, not excel, so not sure what the issue is or how to fix it

thanks for the reply

 

[Micron]

As I mentioned, the issue is likely the data type that the query is acting on is not right. You don't say how you are interacting with the data; i.e. I can't figure out if you're working with a table, Excel spreadsheet via automation or a linked spreadsheet. To me, a pivot is either an Excel table of sorts or it is an Access crosstab query so I can't figure it out from that either. Maybe you're running queries directly or via code.

To say you have 2 databases and a query suggests the query is located somewhere else. Maybe you're using ODBC? Maybe Excel's query tool? You'll have to provide more info on what you have and what you're doing. Or you can use the TypeName() function in code to evaluate what the data type is that you're using.

 

[xenou]

At risk of muddying the waters, can you post the formula in question (and, possibly some sample data)?