Dsum with Nz Causes Overflow

[CementCarver]

I'm having trouble figuring out the syntax of the dsum while using Nz. Here's what I have so far:



building_sq_ft = DSum("Nz([ArenaSqFt],0) + Nz([ShedSqFt],0) + Nz([MaintSqFt],0)", "FULL_COMM_BREAKDOWN", "MEMBER_ID=" & [current_id])

Can anyone see what's wrong with this line? I'm getting a Run Time Error '6' Overflow......

CementCarver

[pbaldy]

Never tried to use it that way, but it might work. What data type is building_sq_ft declared as, and what value would you expect to come from the DSum()?

[CementCarver]

Building_sq_ft is claimed as an integer. And what I'm expecting is to sum all three fields and udpate the total sq. footage field after any updated has happened on those three fields.

I then run a recordset .edit and update the total sq. footage field.

CementCarver

[pbaldy]

I meant what value? An integer data type can only hold 32k. Might the result be higher than that?

[June7]

Member_ID is number field?

I tested, calc works.

But is bad idea to save aggregate data. Calculate the Sum when needed but don't save to table.

[CementCarver]

I need to calculate the sum and store the resultant total sq. footage. June 7, are you saying to NOT have a field to store this total and to keep a running total just as a display item in a field? Right now, I store the actual value in a field called BUILDING_TOTAL.

As for pbaldy, the highest number I'm storing now is 5,475,536, so I think my claiming the variable as an integer is incorrect here.

[June7]

What do you mean by a 'running total'?

Yes, I am suggesting you not save the sum to table. Review http://allenbrowne.com/casu-14.html

[CementCarver]

I meant by saying "running total" is to calculate the sum of those fields when needed to display the result and not store the actual value in the database.

It never dawned on me to just calculate this values from within vba code, and not actually have to store the resultant value in the database. Thankx for your help.

CementCarver

[June7]

Doesn't require VBA code. This calculation can be done with query or in textbox on form/report.