Dsum / Dcount in VBA

[Evagrius]

Hi all,

I have this code at the "Form Current" Event. When a user activates a new record, I wanted to let them know how many times the value in txtOrderAmt appears in the column Order_amt, and also the sum of the values.

So, if 300 is in txtOrderAmt at the current record, then count how many times 300 appears, and the sum - the sum is in a seperate txtbox. I can't get it to work right though. This works on text field, but I am having problem with this because order_amt is not text. I would be grateful for any help. Thank you!

If Not Me.txtOrderAmt = "" Then
    MyDcountI = DCount([order_amt], "MyTable", [order_amt] = Me.txtOrderAmt)
    MyOrderMCount = MyDcountI
    MyDsumI = DSum([order_amt], "MyTable", [order_amt] = Me.txtOrderAmt)
    MyOrderMSum = FormatCurrency(MyDsumI, 0)
    Else
    Me.MyOrderMCount = 0
    Me.MyOrderMSum = 0
End If

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

Note that the quotes are important.

 

[Evagrius]

Thank You very much Pbaldy - It appears the below works. Thanks!!

    If Not Me.txtOrderAmt = "" Then
        MyDcountI = DCount("[order_amt]", "Mytable", "[order_amt]" & " = " & Me.txtOrderAmt)
        MyOrderMCount = MyDcountI
        MyDsumI = DSum("[order_amt]", "Mytable", "[order_amt]" & " = " & Me.txtOrderAmt)
        MyOrderMSum = FormatCurrency(MyDsumI, 0)
        Else
        Me.MyOrderMCount = 0
        Me.MyOrderMSum = 0
    End If

 

[pbaldy]

Presuming the order amount is a numeric field, you want the syntax for that. You're using the syntax for a text field. The error is the one you'd get in that situation.

 

[pbaldy]

Ah, you edited your post. Glad you got it working. Personally I find the extra concatenations harder to read. I'd do this:

DCount("[order_amt]", "Mytable", "[order_amt] = " & Me.txtOrderAmt)

 

[Evagrius]

That's awesome! I didn't think of doing it that way. A thousand thanks Pbaldy! I am always grateful for the extra hints beyond the necessary help