# Thread: (Re-Post) Calculation DSum (A2000)

1. ## (Re-Post) Calculation DSum (A2000)

In a previous post I was trying to total in a subform in a specific way.

I was kindly given the following expression from Charlotte (Thanks) which I still seem to be having trouble with: -

DSum("[TG]","LABOUR BOOKING","[OPERATION]='PAINT' AND [ESTIMATENO] = " & Me![EstimateNo])

I tried a different way using a query and summing etc.
This worked for a while but kept tripping up because two subforms were trying to access the same table at the same time.
Hence the Re-Post, I really need to calculate the sum in this specific way.
Here goes, I will try to explain.

tblLabourBooking has the following fields.
[Date]
[EstimateNo]
[Supp]
[Operative]-Looksup the Operatives name
[Operation]-Looksup the Operation, "Paint", "Body" or "Fit"
[TG]
[TT]

When entered in the table, I will get something similar to the following :-

[Date]=23/07/02 | [EstimateNo]=12345 | [Supp]=0 | [Operative]=Willett | [Operation]=Paint | [TG]=10 | [TT]=9.5
(Shortened without field names):-
23/07/02 | 12345 | 0 | Willett | Paint | 10 | 9.5 |

So a few records could be as follows.

23/07/02 | 12345 | 0 | Willett | Paint | 10 | 0 |
23/07/02 | 12345 | 0 | Jones | Body | 14 | 0 |
24/07/02 | 12345 | 0 | Smith | Fit | 12 | 0 |
24/07/02 | 12345 | 0 | Willett | Paint | 12 | 0 |

Notice two entries for Paint with the same [EstimateNo] of 12345, This is quite normal.
The data is entered via a subform on a daily basis.
In the form footer I can total the two numeric fields quite easily: -
=Sum([TG])
=Sum([TT])
And from here reference it on the main form to show the total value of [TG]-Time Given or [TT]-Time Taken.

Here's the twist.
Instead of calculating All of [TG] or [TT] I need to drill down a little further.

On the main form I want to show the total sum value of [TG] or [TT] where the [Operation] is "Paint" with EstimateNo 12345/0
"""""""""""""""""""""""""""""""""""""""""""""""""" """"""sum value of [TG] or [TT] where the [Operation] is "Body" """""""""""""
"""""""""""""""""""""""""""""""""""""""""""""""""" """"""sum value of [TG] or [TT] where the [Operation] is "Fit" """""""""""""
So each time a record is changed I will get the "Paint" "Body" "Fit" values for the Current EstimateNo.

I tried Charlottes expression which returned errors, and tried to edit it with no success.

As I said earlier, This calculation does need to be specific.
Any Help is appreciated.

Dave

2. ## Re: (Re-Post) Calculation DSum (A2000)

Dave,

If your table is named tblLabourBooking, you should use that name in the DSum expression too.

I have attaches a zipped Access 97 database with two tables, a form and a subform.
The main form contains text boxes that sum TG and TT for each operation for the current EstimateNo, i.e. sum the appropriate values in the subform.

HTH,
Hans

3. ## Re: (Re-Post) Calculation DSum (A2000)

Sorry for Typo LABOUR BOOKING, I had noticed and alterred to suit but still didnt work.

Its all the Operands " ' , & "'" which confuse me.

May I just say, I have been messing with this for ages and pulling my hair (What bit I have left) out and not getting any where.

In the space of , I don't know 45 minutes, you have solved my calculation task.
I am physically sickened by the sheer knowledge floating around in hyperspace.
I suppose thats a definition of jealousy.

Thanks Hans for the attachment, I see you went to some trouble for me once again.
I am for-ever in your debt.
If you are ever in the position of having a labotomy, send me the little bit I need.

Seriously, Thanks to all participants.

Regards Dave.
Have a nice day.

4. ## Re: (Re-Post) Calculation DSum (A2000)

Hans
Can we include possibly a NZ part to handle Nulls

=DSum("TT","tblLabourBooking","EstimateNo=" & [EstimateNo] & " And Operation='Paint'")

Dave

5. ## Re: (Re-Post) Calculation DSum (A2000)

Dave,

Yes, you can:

=Nz(DSum("TT","tblLabourBooking","EstimateNo=" & [EstimateNo] & " And Operation='Paint'"),0)

There is one caveat: the NZ function has a tendency to convert values to text, even if both arguments are numbers.

If you don't want to do further calculations with the sum, that isn't a problem. If you like, you can set the text box to right align the contents.

If you need the results for further calculations, you can convert them to numeric using one of the conversion functions.
Val converts to a number in general.
CLng converts to a long integer.
CDbl converts to a double precision floating point number.
(etc.)

So for instance:

=Val(Nz(DSum("TT","tblLabourBooking","EstimateNo=" & [EstimateNo] & " And Operation='Paint'"),0))

6. ## Re: (Re-Post) Calculation DSum (A2000)

Guess what. Divide by Zero

Not all the fields contain data or zero's. 0 / 0 = error.
I think I remember an IIF statement will cure this.

Just a little more help and I will go away.

Thanks
Dave

7. ## Re: (Re-Post) Calculation DSum (A2000)

Cheers

=IIf([txtGlassTG]=0,0,[txtGlassTG]/[txtGlassTT])

Dave

8. ## Re: (Re-Post) Calculation DSum (A2000)

Try something like

=IIf([txtSumTTBody]=0,0,[txtSumTGBody]/[txtSumTTBody])

where txtSumTGBody and txtSumTTBody are the names of the text boxes containing the sum of TG and TT, respectively.

Analogously for other operations, of course.

If you want the effectivity text box to remain empty if txtSumTTBody is 0, use

=IIf([txtSumTTBody]=0,Null,[txtSumTGBody]/[txtSumTTBody])