Report Numbers display "-2E-15" instead of "0"

[AC5FF]

I am hoping this could be an easy one, but for the life of me I cannot figure it out.

I have a payroll database and my report that lists vacation time is not displaying correctly (for one person only). Every entry into my payroll tables are in this format: ##.## (24hr) I've double checked all entries that they are entered in this fashon, and found nothing out of the ordinary.

So when I run my report's query I grab VACATION_EARNED from my EMPLOYEE table; subtract VACATION_USED (as a sum from my PAYROLL table) and subtract VACATION_SCHED (as a sum from my FORCAST table) to get VACATION_REMAINING.

if I look at this in a query instead of a report; my results are exactly what I would expect to see. I.E. JohnDoe, 40, 32, 8, 0 Yet when I view the data in the report, instead of the 0 showing for VACATION_REMAINING I get -2E-15

Now I recognize this as a scientific notation, but why?!?! I've set the report field's properties and backtracked all my number fields to be sure all of them are set to the same properties; I just cannot find out where/how this is happening...

Any ideas??

 

[Foe]

Is that the only 0 (zero) present?

 

[AC5FF]

No, it is not. This report lists 6 people. 5 of the 6 have 0 remaining vacation time.

The one with remaining vacation time displays correct (4.32)
Four of the 5 with zero display "0"
The one displays the "-2E-15"

 

[spikepl]

DateTime is represented by Double variables, where the time is a fraction of the day 1/(24*60*60), i.e. time is not represented exactly. When calculating a difference between two Double variables you cannot expect a 0, but some numerical noise. Did you use DateAdd and DateDiff ? These functions, I could imagine, "clean" the results.

Update: Numerically, your result is not incorrect. For display, set the format property appropriately, and the problem should be gone.

 

[AC5FF]

I've never been able to figure this out - or at least to where it makes sense to me;
but double variables/intergers/etc always confuses me.

I did not set up the database, but as I have been digging through this to figure out why I do remember seeing something set to 'double' ... not sure how/why. As for time; the data is entered as intergers ... I.E. 14.35 = 2:21pm The data is not figured as part of Access; it is entered by what our time clock stamps on the cards.

I'm going to see if I can find that 'double' setting and see if changing it does anything. Will let you know..

 

[spikepl]

14.35 is not an integer. Integers have no decimal. To get 14.35 represented exactly as 14.35 you need to set the type of the variable to Currency (which is exact, with 4 decimals, because of an internal fiddle).

 

[AC5FF]

Okay; so the table was set to 'DOUBLE' and 'STANDARD'.
If I change this to 'INTEGER' and 'STANDARD' during save changes it says some data may be lost. No problem since I am working on a backup table

The report now displays the "0" instead of the "-2E-15" but everything is rounded to whole digits, I've lost display on decimal places.

Update: Numerically, your result is not incorrect. For display, set the format property appropriately, and the problem should be gone.

I assume here that you mean to check the properties of the report field that is displaying the '-2E-15' ... That was the first thing I did. Initially the format was blank; but I change that to "General" and "FIXED" but it did not change anything. The decimal field was set to "AUTO" and I also changed that to "2" and again did not change anything...

 

[AC5FF]

14.35 is not an integer. Integers have no decimal. To get 14.35 represented exactly as 14.35 you need to set the type of the variable to Currency (which is exact, with 4 decimals, because of an internal fiddle).

to Currency?? When I did that all the data in my table changed to $7.32, $0.00, etc... That isn't right is it???

Gotta run for tonight; will try and pick this up in the AM

 

[AC5FF]

I got it working....
Talked the "Currency" idea over with one of my programmers and he thought that was a heck of an idea! Only thing I didn't like about it was needing to strip off the "$" symbol from everywhere.

Instead, in my report, I just used =round(sum(xxxxxxxx),2) This took care of the odd data I was seeing without having to change data tables/etc...

Spike; I appriciate your assistance here! I still need to research what "Double" "Single" "Long Integer" "Short Integer" etc all mean and their differences... But you did help me step through some problems here and I wanted to say thanks!

AC