Why does Access Calculated fields in table round down to £0.00?

[MackMan]

Ok, so I need to store a calculated field in a table in order to show a running sum in queries (the table is based on a [Credit]-[Debit] = [£###.##]'s scenario as I'm not sure how this will work in a running total query)

both the Credit and Debit fields in the table are set to "Currency" with a decimal of 2, likewise with the calculated field on the table, currency again, with a decimal of 2.

It all works brilliantly.. unless I enter a value less than £0.51.. where the calculated field rounds down to£0.00.

I've been scouring the net and this forum for days.. and nothing.

Why does access do this?

Your thoughts...

 

[CJ_London]

well first a calculated field will not provide you with a running sum

unless I enter a value less than £0.51

Not sure what you mean here - don't you need two values? one for debit and one for credit?

I would expect this behaviour if either field is null since any calculation with a null in it will produce a null value. To avoid it, you need to set the required property for both debit and credit to true and the default value to 0

I suggest you would be better to have a single field where debits are positive and credits negative

Much easier to add/sum, probably quicker than using a calculated field and would use the same functionality for a running sum.

 

[The_Doc_Man]

1. As soon as you say "Sum" you should probably do something in a query. Computing a value in a table is almost always the wrong answer.

2. Odd behavior for numerical rounding/truncation usually occurs because the number isn't the data type that you thought it would be. This is particularly true when you are talking about a value to be computed. You can specify any display format you want but if something is an integer it isn't going to be easy to display it as a fraction.

 

[MackMan]

CJ / the DOC

Hi.

well first a calculated field will not provide you with a running sum

It's does as it's the calculated field of debit and credit, and seeing as I'm not sure how you can calculate a running sum using Both fields [credit] - [debit] from a table, I thought this would be the obvious answer (we all learn by our mistakes, and the pro's on Access, like yourself), however, it's turned out not to be.

As for using one field for both positive and negative numbers, I need to visually see a credit field and a debit field to differentiate between what are credits and debits (both inputted on a separate tabbed form)

what I don't understand about the calculated field is that it all works perfectly for values above 0.51. for example.. if entering £6.52 (say) it calculates that amount, but with values lower than 0.50 it rounds down. So something tells me it "Can" do it... but there's something not quite right with the calculation it's doing (despite all fields being the same type)

So On this note, I've deleted all calculated fields from the table, and gone without running sums, as, like I say, not too sure how to calculate using two fields as opposed to one.

Appreciate your comments and thoughts guys!! Many thanks

 

[CJ_London]

I take your point about having separate fields for debit credit, I just manage it in the form rather than the table. It is really a matter of personal choice.

You calculate a running sum in a query, not a table. However you can also have a running sum control in a report, but not a form. Certainly for 2010, not sure about earlier versions.

To calculate a running sum in a query, your table needs as a minimum the following

tblID
tblDate
tblValue

the query would be something like

 SELECT tblDate, Debit, Credit, DSum("[Debit]-[Credit]","myTable","tblDate<=" & tblDate & " AND tblID<=" & tblID) AS RunningSum
 FROM myTable
 ORDER BY tblDate, tblID

or if you want to use a subquery (which will be quicker for larger datasets) it would be

 SELECT tblDate, Debit, Credit, (SELECT Sum(Debit-Credit) FROM myTable as T WHERE tblDate<=myTable.tblDate AND tblID<=myTable.tblID ) AS RunningSum
 FROM myTable
 ORDER BY tblDate, tblID

The tblID (or something similar) is required so that there is an 'order' applied if you have more than one transaction in a day

With regards the calculated field, I'm not sure what you are doing. A little test produced the following result - the calculated field is val3. You can see that where the other figure is blank (i.e. null) that a value is not calculated

However if you are talking about rounding, then note that the currency datatype actually goes to 4 decimal places, the fact you have specified 2 is applied to formatting only. Also, and someone can correct me if I am wrong, the currency datatype is subject to bankers rounding - this is a link to explain

http://www.xbeat.net/vbspeed/i_BankersRounding.htm

 

[MackMan]

CJ! If I could buy you a beer. I would! You Sir (or Madame for that matter) are awesome. I thought I'd have to reference the [debit] and [credit] fields that are different parts of the query in relation to the tables they sit in! had no idea "[credit]-[debit]" from the same table was that simple!

Heres what I've been running to get the Runtot: (where transTotal was the calculated field in the old table)

RunTot: [forms]![frmMain]![txtopenbal]+DSum("[transtotal]","[tblTopLines]","[AccountID]=" & [AccountID] & " AND [TopLineID]<=" & [TopLineID])

If I change it to this: it should work? right...

RunTot: [forms]![frmMain]![txtopenbal]+DSum("nz([credit],0)-nz([debit],0)","[tblTopLines]","[AccountID]=" & [AccountID] & " AND [TopLineID]<=" & [TopLineID])

My access is at work so I won't have a chance to try it until Monday.

 

[gemma-the-husky]

to be honest, if I have values that are either debit or credit, I would store in a single "value" column with the sign. Then I can just sum a single field without needing to mess around with debits and credits, using union queries, worrying about nulls, and all the other problems.

 

[CJ_London]

If I change it to this: it should work? right...

with regards the summed values, yes, but I would say is the opening balance should be a transaction (of type 'opening balance' as opposed to 'deposit', 'withdrawal' etc) and not a value in say the account table.

Also I don't know your table structure or the full query this comes from, but

a) using the ID field on its own relies on data being input in the right order, if it is possible this is not the case, you need to include a date field in the dsum where clause and sorting of the main query. e.g. your calc will produce

id=1, date =1/2/2014, value=400 runningsum=400
id=2, date =1/1/2014, value=200 runningsum=600

my example
id=2, date =1/1/2014, value=200 runningsum=200
id=1, date =1/2/2014, value=400 runningsum=600

b)the main query also needs to be sorted by ID otherwise the running sum will be jumping all over the place

c) if you have a large number of transactions, as previous advised, a subquery will be quicker

 

[MackMan]

Worked like a treat! Never again will I use Calculated Fields in a table. Thanks for the advice! Much appreciated!

Have a beer!

 

[CJ_London]

no problem!