Weird error in tables

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

I have a form that is posting a transaction amount straight to
tbl_transactions. I have a crosscheck on this same form
(frm_Transactions) that will not allow someone to post a transaction
that will bring the balance on a given loan below zero. It gets this
balance from a crosstab query that sums up the amount of all
transactions for every loan. This has worked fine for a few hundred
loans and all of the sudden one of my users is trying to post a
transaction to bring the loan balance to $0 and it will not let her
because it thinks the loan balance is going to be below zero. So I
went directly to the table, posted the transaction to avoid the form's
crosscheck and went back to the form. If I try to make another
transaction it says the current loan balance is
7.27595761418343E-12 ...How this weird balance got in there I have no
idea. My amount field in the table is number-double and limited to 2
decimal places as we are always using currency. I have tried actually
recreating all the transactions in the table but it still will come up
with some weird fractional balance in my crosstab query.

Any suggestions???
Thanks for the help,
Brian
 
Good old floating point math. Computers sometimes have problems doing it.
Here's another example:

Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not exactly the
same as -0.01. Close but no cigar.

Suggestion? Set the field to currency instead of double. That should fix it
and you're dealing with money anyway.
 
Jerry thank you for your response. I currently the current settings in
my table:
Field Size: Double
Format: Currency
Decimal Places: 2

For field size currency is not an option I don't believe. I have:
Byte, Integer, Long Integer, Single, Double, Replication ID, Decimal

Let me know what you think - I appreciate your help.

-brian
 
Jerry thank you for your response. I currently have the following
settings in
my table:
Field Size: Double
Format: Currency
Decimal Places: 2

For field size currency is not an option I don't believe. I have:
Byte, Integer, Long Integer, Single, Double, Replication ID, Decimal


Let me know what you think - I appreciate your help.
 
Jerry I feel mildly retarded but I didn't even look at the data type
up to. I changed it to currency and I believe my problem is fixed.
Thanks for your help.

-Brian
 
Somewhat confusing as Access will let you format a number to LOOK like
currency and also has a Currency data type.

You want to change the "data type" from Number to Currency. Before doing
that make a copy of the database or at least table first. From your
description, you may have some data in the table that can't be converted to
currency or might round a cent the wrong way.
 
Back
Top