Access query outputs correct values, linked Excel table shows zero's

[towners]

Hi,

Appreciate any thoughts on this...

I have an Access query that populates an Excel spreadsheet using external data connection. I use this method a lot and am a little stumped with this problem.

The Access query contains a number of fields that total up the sum of other fields in the dataset, when viewed within the query results (in Access) the total fields calculate correctly, however when I refresh the data from Excel the total fields are populated with zero's... All other fields are ok.

Obviously it's something with the total fields but I can't work out what.

Any help gratefully received.

Paul

 

[par60056]

Would it be possible to see the query?

Are the totals > 1? Could it be a cell formatting issue or are you examining the cell contents?

I don't query Access. Are you using the "From Access" or "From Other Source"?

Just hoping I can poke something that will cause you to figure it out Or moving the question back up the list where somebody will see it.

 

[towners]

Hi, thanks for the response.

I'm not sure how I can show you the whole query, it's quite large and compiles data from many other tables to create a single normalised data set. The field that causes me the problem is a simple total:

PayableServiceTotalCost: [PayableServiceAccomCost]+[PayableServiceEquipmentCost]+[PayableServiceFieldManagerCost]+[PayableServiceFreightCost]+[PayableServiceLabourElectricsCost]+[PayableServiceLabourInstallerCost]+[PayableServiceMaterialsCost]+[PayableServiceOtherCost]+[PayableServiceSurveyCost]+[PayableServiceTravelCost]

The values for each of the individual summed fields are taken from a joined table and replaced with zero if the field is null.

 

[par60056]

Obviously if all the fields are costs it is REALLY unlikely that everything is a 0 or very small (E-05) number. So it should not be a number formatting issue.

Is it a stored query in access? If not can you copy the text of the query from Excel and make a new query in Access and paste in the SQL to see if there might be some small issue with a join or parameter that is messing up the result?

I pull back data from multiple databases all the time and every issue has come to an error in the query someplace.