none
DSum with condition RRS feed

  • Question

  • Hi Experts,

    I have a form which [NumOfDays] and [AnnualLeaveTypeID] (ComboBox), i try want to sum up the [NumOfDays] which condition [AnnualLeaveTypeID] = 3.

    I have a TextBox and with below formula in 'Control Source':-

    =dsum("[NumOfDays]","[tblAnnualLeave]","[AnnualLeaveTypeID] = 3")

    I only able to get the total [NumofDays] ; [annualLeaveTypeID] is no working. Correct result is when i change the [AnnualLeaveTypeID](ComboBox) it will effect the Sum of [NumOfDays]

    Kindly advise my formula is wrong? Thanks.

    Regards,

    Danny

     

    Wednesday, July 13, 2011 4:05 AM

Answers

  • Hi Danny

    Danny Gan wrote:

    I have a form which [NumOfDays] and [AnnualLeaveTypeID] (ComboBox), i try
    want to sum up the [NumOfDays] which condition [AnnualLeaveTypeID] = 3.

    I have a TextBox and with below formula in 'Control Source':-

    =dsum("[NumOfDays]","[tblAnnualLeave]","[AnnualLeaveTypeID] = 3")

    I only able to get the total [NumofDays] ; [annualLeaveTypeID] is no
    working. Correct result is when i change the
    [AnnualLeaveTypeID](ComboBox) it will effect the Sum of [NumOfDays]

    Kindly advise my formula is wrong? Thanks.

    The Formula looks ok for me and it seems to work when you select something in the Combobox. But there may be a problem with the resolution of [anualLeaveTypeID]. If this is the name of your combobox and the name of the field in the database then the DSum() may use the value in the Combobox. To prevent this you should rename the combobox and use a name like cboAnualLeaveTypeID.

    You also may use following statement instead:
    =DSum("[NumOfDays]","[tblAnnualLeave]","[tblAnnualLeave].[AnnualLeaveTypeID] = 3")

    to ensure the expression service don't use Form("tblAnnualLeave") in the filter.

    If this doesn't help you may try following in the Form_Current Event:

    Me!NumOfDays.Requery

    This should requery the Sum after the date is loaded to the form. But again: If the resulution of [tblAnnualLeave] goes to the form you will get the sum only if you select the entry in the combobox that has the ID = 3.

    HTH
    Henry

    • Marked as answer by Danny Gan Wednesday, July 13, 2011 8:16 AM
    Wednesday, July 13, 2011 6:59 AM

All replies

  • Hi Danny Gan,

    have a look:

    http://office.microsoft.com/en-us/excel-help/dsum-HP005209069.aspx

    and here:

    http://www.techonthenet.com/excel/formulas/dsum.php


    Please 'Mark as Answer' if I helped. This helps others who have the same problem!
    Wednesday, July 13, 2011 5:02 AM
  • Hi Dennis,

    It is Access vba not Excel vba. thanks

    Regards,

    Danny

    Wednesday, July 13, 2011 5:53 AM
  • ok,

    have a look:

    http://office.microsoft.com/en-us/access-help/dsum-function-HA001228829.aspx?CTT=1


    Please 'Mark as Answer' if I helped. This helps others who have the same problem!
    Wednesday, July 13, 2011 6:54 AM
  • Hi Danny

    Danny Gan wrote:

    I have a form which [NumOfDays] and [AnnualLeaveTypeID] (ComboBox), i try
    want to sum up the [NumOfDays] which condition [AnnualLeaveTypeID] = 3.

    I have a TextBox and with below formula in 'Control Source':-

    =dsum("[NumOfDays]","[tblAnnualLeave]","[AnnualLeaveTypeID] = 3")

    I only able to get the total [NumofDays] ; [annualLeaveTypeID] is no
    working. Correct result is when i change the
    [AnnualLeaveTypeID](ComboBox) it will effect the Sum of [NumOfDays]

    Kindly advise my formula is wrong? Thanks.

    The Formula looks ok for me and it seems to work when you select something in the Combobox. But there may be a problem with the resolution of [anualLeaveTypeID]. If this is the name of your combobox and the name of the field in the database then the DSum() may use the value in the Combobox. To prevent this you should rename the combobox and use a name like cboAnualLeaveTypeID.

    You also may use following statement instead:
    =DSum("[NumOfDays]","[tblAnnualLeave]","[tblAnnualLeave].[AnnualLeaveTypeID] = 3")

    to ensure the expression service don't use Form("tblAnnualLeave") in the filter.

    If this doesn't help you may try following in the Form_Current Event:

    Me!NumOfDays.Requery

    This should requery the Sum after the date is loaded to the form. But again: If the resulution of [tblAnnualLeave] goes to the form you will get the sum only if you select the entry in the combobox that has the ID = 3.

    HTH
    Henry

    • Marked as answer by Danny Gan Wednesday, July 13, 2011 8:16 AM
    Wednesday, July 13, 2011 6:59 AM
  • The formula is correct. I think that problem is in AnnualLeaveTypeID field definition, if you use lookup check is your field defined as Number and bounded column is numeric too.
    Wednesday, July 13, 2011 7:01 AM