Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Cheers

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


    Dave

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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])

    instead.

    Regards,
    Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •