locked
DSum based on 2 values RRS feed

  • Question

  • I have CustomerTable which has fields [ContractPrice], [RepID], [BalanceDue], [MatCon] as well as about 90 other fields in it. Contract Price is a currency amount, RepID is an autonumber from a different table, Balance due is another field of currency, and MatCon is the value 1 for no and 2 for yes. If matCon value = 1 (no), the ContractPrice field is blank, because we dont have a contract yet. I created a form with a RepID combo box that has a row source from a RepTable. When I choose the RepID 1 from combo box, I want it to add all the [contractPrice] field where the repID = 1. There are 2 ways I tried this. First I tried this. 

    CurContractPrice = DSum("[ContractPrice]", "CustomerTable", "[Rep]" = Me.cboRep)
    Me.txtContractPrice = CurContractPrice

    I keep getting a null error, because it is looking thru a field that has a lot of blanks. I tried a Nz but it didn't work. I found a lot of different combos and tried them all. This is actually the real field names below

    CurX = Nz(DSum("[TotConPri]", 0), "CustomerTable", "[Rep]" = Me.cboRep)
    Me.txtTotBal = CurX

    My other option is to search customer table for 2 criterias. MatCon value = 2 (yes) and Rep ID = cboRep (RepID from rep table). I am looking for only jobs that have contracts written (MatCon value = 2), then for the repID, and summing only those ones.I tried that using DSum

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = '2'" And "[Rep]" = Me.txtRep)

    Me.txtTotBal = CurX

    I even thought of an If statement, but I am not really advanced in vba so anything takes a lot of trial and error for me.


    I am like a lava lamp. Not too bright but a lot of fun to watch.

    Wednesday, May 16, 2012 4:21 PM

Answers

  • For the single condition, try

    CurX = DSum("[TotConPri]", "CustomerTable", "[Rep] = " & Me.txtRep)

    For the two conditions, try

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = 2 AND [Rep] = " & Me.txtRep)

    This assumes that MatCon and Rep are both number fields. If MatCon is a text field:

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = '2' AND [Rep] = " & Me.txtRep)

    And if Rep is also a text field:

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = '2' AND [Rep] = '" & Me.txtRep & "'")


    Regards, Hans Vogelaar

    • Marked as answer by overdhill1 Thursday, May 17, 2012 3:16 PM
    Wednesday, May 16, 2012 4:29 PM
  • The expression

    CouPro = DCount("[TotPaid]", "CustomerTable", "[TotPaid] > 0 AND [BalDue] > 0")

    should work if TotPaid and BalDue are number or currency fields. The VBA function for the current date is Date(), so the second one should be

    CouAdjDate = DCount("[AdjusterApptDate]", "CustomerTable", "[AdjusterApptDate] >= Date()+1")


    Regards, Hans Vogelaar

    • Marked as answer by overdhill1 Thursday, May 17, 2012 3:16 PM
    Thursday, May 17, 2012 8:35 AM

All replies

  • For the single condition, try

    CurX = DSum("[TotConPri]", "CustomerTable", "[Rep] = " & Me.txtRep)

    For the two conditions, try

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = 2 AND [Rep] = " & Me.txtRep)

    This assumes that MatCon and Rep are both number fields. If MatCon is a text field:

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = '2' AND [Rep] = " & Me.txtRep)

    And if Rep is also a text field:

    CurX = DSum("[TotConPri]", "CustomerTable", "[MatCon] = '2' AND [Rep] = '" & Me.txtRep & "'")


    Regards, Hans Vogelaar

    • Marked as answer by overdhill1 Thursday, May 17, 2012 3:16 PM
    Wednesday, May 16, 2012 4:29 PM
  • That worked awesome, can I ask one more question? I am also trying to to count jobs in production. I was able to adapt the dsum to the dcount. Error keeps coming up as mismatch, and I am sure it is quite simple. My two criterias for jobs in production is [totPaid](currency) and [BalDue](currency). If they have given me a down pymt, but job is not paid in full, then it is still in process or production. My code is like this:

    CouPro = DCount("[TotPaid]", "CustomerTable", "[TotPaid] > 0 AND [BalDue] > 0")

    Me.txtPro = CouPro

    To ask if it = text value you wrap in quotes. Number value is no quotes.  CouAdjDate = DCount"[AdjusterApptDate]","CustomerTable", [AdjusterApptDate] >= today+1. This woold be to display tomorrow's appts. Not sure about how to write it. Quotes, Double Quotes, and Parenthesis.

    Thanks so much


    I am like a lava lamp. Not too bright but a lot of fun to watch.

    Thursday, May 17, 2012 1:43 AM
  • The expression

    CouPro = DCount("[TotPaid]", "CustomerTable", "[TotPaid] > 0 AND [BalDue] > 0")

    should work if TotPaid and BalDue are number or currency fields. The VBA function for the current date is Date(), so the second one should be

    CouAdjDate = DCount("[AdjusterApptDate]", "CustomerTable", "[AdjusterApptDate] >= Date()+1")


    Regards, Hans Vogelaar

    • Marked as answer by overdhill1 Thursday, May 17, 2012 3:16 PM
    Thursday, May 17, 2012 8:35 AM