That looks like thje Excel feature¹ of adding up lots of decimal values which should be zero but due to the weird non-ISO mathematic [sic] routines that Excel apparently uses, you get a ridiculously small value instead.

I know you can't do it here (I think), but everywhere I have to add/sum()/etc any 2 or more numbers to 2 decimal places in Excel at work I automatically enclose the sum in ROUND(<sum>,2) - something I don't have to do in LibreOffice at home.

The best I think you can do is to format that column - eg something like "#,##0.00_);[red](#,##0.00);._0_0_)" - to hide these mathematical rounding errors and keep the format on refreshing data.  Then when you use the data, round as appropriately.

¹From an Apple ][ manual glossary: Feature := bug as described by the marketing department.

But this is MS Query running a Sage ODBC extraction so it's not Excel that's putting it there but the data that it's received into those cells. SO it might even be a MS Query issue

You are now getting into the realms of how exactly Excel sends the ODBC query and exactly what data exactly is returned, which is beyond my ken.

It is curious that these lines do end up with a very small value that indicates to me that some non-ISO floating point mathematic _[sic]_ has taken place.  Out of interest, are the figures coming directly from a single transaction in Sage or are they the result of summing more than one?

It could be that Sage has the rounding error stored (due to mathematical operations being processed on the data), but its display formats automagically correct it to 0; however on export it sends the value as is - complete with rounding error(s) - and leaves it to the receiving end to sort it out.

Robert N said:

Out of interest, are the figures coming directly from a single transaction in Sage or are they the result of summing more than one?

Query is creating multiple records based on the Material Transactions that I'm extracting

Sorry, I wasn't too clear: I meant that for each individual line in the resulting table do the figures come from a single transaction or do they come from multiple transactions (in Sage) summed together?

I suspect the former¹...

From what I understand of report designer, it gets individual transactions via ODBC and then it itself sums them if necessary - this can be seen with, say, the Transactional TB where when exporting the data to Excel you get all the individual transactions whereas exporting the report you only get the sum for each nominal.

However, if it is the latter, then it is this summing process that is creating the 1.13687x10^-13 (= 0.000000000000113687) rounding errors due to 0.1 not having a terminating binary representation (and not being sensible when it comes to considering the value that is obtained from the summing process and if it's near-enough zero then making it zero).

The only question arises as to what summing process it was that created this rounding error.

As I say, my best response is to format the value to 2 decimal places (as it's money you're referring to) in the receiving table and then on further use round as appropriate.

Others may have a better idea.

¹possibly with processing within Sage that may have resulted in the rounding error which Sage then stores and "ignores" as it's near enough zero when compared to 2 dp, but faithfully pushes out to the ODBC connection, leaving the receiving end to tidy up.