Linked Access Data Table not acting as I would expect

[mnty89]

Hi all,

Sort of a hybrid Excel/Access question possibly. I have a database that has a query which feeds into a direct connected sheet in my workbook.

I inserted it as a table, and the weird thing is that it populates all of the numbers, out to several decimal places. Looks like 10-15 decimal places.

I wanted to do sum aggregates based on some sub categories in there(yes I know I could do this in access and feed it over). The weird thing is the data present in the table is not reacting to formulas the way "normal" excel data would. For example, if I do an averageif statement it will return a #DIV/0 error. But, if I click into the cell then click out of it, it shifts the data to the right of the cell and then my formula treats it like it would any other cell data. I also had this problem when I tried to feed this data in as a direct pivot as opposed to the table, and when I tried to select anything other than counts it treated it as if was an error. Any idea why this is happening/how to fix it?

 

[Fazza]

Maybe Excel is interpreting the field as text?
This might happen if there are a lot of nulls - but should not if the field has numeric entries in every record.

This might depend on how it is set up. I'm assuming the workbook has a query to the database: edit the SQL in the workbook to explicitly force numeric values.

if the source data is purely numeric, then I would think unnecessary but maybe somehow it helps: SELECT CDbl(field name) AS [field name]

if either numeric or nulls and it is OK to convert them to zero: SELECT IIF(field name Is Null, 0, field name) AS [field name]

or some variation of this idea

to check the starting assumpion, have at look at the data in the database and see if the field is numeric, and then if every record has a numeric value (or some are nulls)