Query giving strange results

[deepakg27]

Hi guys,



I am working on a production database. (I am Attaching copy of database for your ready reference.)

In my data base I have entered the values of the production. (production field is set to Number with double, decimal places 2 , format standard).

Now When I try to query my total production from 1st August 2017 to 31st January 2018, I get dayshift result in 9368.76000000001 where as the result should be 9368.76. Cant get from where .000000001 is being added and displayed in the result. Further if I shift the date from 1st September 2017 to 31st January 2018, no extra zeroes and 1 is shown. Even so if I set the date range to 1st August 2017 to 31st December 2017 then also no zeroes are shown.

Only when the date is set to 1st August 2017 to 31st January 2018, the problem occurs. (Even so if the date is set to 10th August to 31st January 2018 the problem does not occur). Please look into it and help. I can avoid the trouble with setting the format properties for the field in query but would like to find where the problem is. It could trouble me in future in some bigger calculations, hence please help and guide.

Awaiting your guidance. (File Attached.)

Thanks and Regards
Deepak Gupta

[isladogs]

I haven't looked at your database

For info, Access calculates dates to 1 second accuracy and stores these as double number datatype ...so you are using this in your calculations.
1 day = 1; 6 hours = 0.25; 1 second = 0.000011574 (approx)

Double is subject to floating point (or rounding) error which has almost certainly triggered the .000000001 discrepancy.
This amounts to a time interval of around 0.1 milliseconds if my calculation is correct - meaningless however

There's nothing magical about those dates, other date ranges could also trigger similar discrepancies
Either ignore it or round to a specified number of d.p. e.g. 2

HTH

[deepakg27]

Thanks for the help.

[orange]

Deepak,

You said

I am working on a production database

.

I hope in reality you are working on a copy of your production database.