So confused - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

So confused

Sandra Truax

4 years ago

Would someone look at my comments (at the end) and my code and tell me what the heck is going on?  I'm so confused and a little frustrated.

Private Sub Form_Current()
        TotalAmountCleared
End Sub

Private Sub TotalAmountCleared()

    Dim TC As Double, TCD As Double, TCP As Double
        Dim TNC As Double

    TCD = Nz(DSum("Deposit", "TransactionT", "AccountID=" & Forms!startupf!AccountCombo & " AND TransactonYear=""" & Forms!startupf!YearFilter & """"), 0)
    TCP = Nz(DSum("Payment", "TransactionT", "AccountID=" & Forms!startupf!AccountCombo & " AND TransactonYear=""" & Forms!startupf!YearFilter & """"), 0)

    'SQL3 is an unbound field to display the results

    'SQL3 = TCD
        ' Total for TCD is 31081.95
    'SQL3 = TCP
        ' Total for TCP is 30347.82
    'SQL3 = TCD - TCP
        ' Total for TCD - TCP is 734.130000000016
        ' WHAT THE HECK!!! How can you end up with a decimal like .130000000016 from just subtracting?
End Sub

	Adam Schwanz @Reply 4 years ago
It must be because of this new Common Core stuff they're teaching... lol

	Adam Schwanz @Reply 4 years ago
I would just do Dim TCD as Currency Dim TCP As Currency :P

	Sandra Truax OP @Reply 4 years ago
That's hilarious! You just made my day Adam!

	Sandra Truax OP @Reply 4 years ago
You continue to amaze me! Changing Double to Currency did the trick. I tried everything but that! Thank you SO much! I'm beginning to wonder if I'm ever going to get this. Can you recommend which of the SQL courses would help me with this type of code. Unfortunately, I can't afford all of them.

	Adam Schwanz @Reply 4 years ago
I found the Access SQL Seminars part 1 and part 2 pretty useful, But this isn't really a SQL thing. Just a variable data type thing. Currency would limit you to 2 decimal places. You could do the same with how you had it as a decimal and use rounding. Just to make sure you have clean ending numbers. These 2 might help? Variables Scope

	Sandra Truax OP @Reply 4 years ago
I will watch those two again. I watched them once, but then couldn't remember what their names were and couldn't find them again. Thanks for the links.

Kevin Yip @Reply

4 years ago

Adam is correct here.  The high number decimal places (15) allowed by Double is the cause of this.  Single and Currency allow fewer decimals, so they don't cause this kind of rounding error.  This has to do with how such numbers are stored in computer memory. Also, beware of constants with decimals, which Access treats as Double.  For instance, if you write this VBA code:

     If (0.1 + 0.2) = 0.3 Then DoSomething

Then this condition will never be true because of the rounding error.  You can try it in the VBA editor's immediate window (see picture below).

Needless to say, in my 20+ years of using Access, I have rarely used Double.

	Kevin Yip @Reply 4 years ago

	Sandra Truax OP @Reply 4 years ago
Thank you Kevin for the information. I had not idea about the issues with using Double. I don't know why I didn't think of currency, especially since i was dealing with money. I just kept thinking interger, and long, and double. I'll blame it on lack of sleep. :) For some reason, I get totally confused on what type to use and when.

	Sandra Truax OP @Reply 4 years ago
Okay, my porch light just came on and I remember where I saw what to use and when! Access Developer 3 explains it all. Watching it again now. Thanks everyone for your help.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View