DSum Function When Lookup filed is a Value List in Table Design

[NTF73]

Hello!

I am trying to report on a payment table by payment method ie 'Cash' and 'Cheque'. In the Table Design I have a text field called 'Method' which is a Value List.

I have created a module with the following function:

Public Function ClassCash() As Currency
Dim cClass As Currency
cClass = Nz(DSum("Price", "ClassPayment", "ToPay=No AND DateUpdated=Forms!ReportDates!txtFrom AND Method=Cash"), 0)
ClassCash = cClass
End Function

In the report I have a text box with the control source of =ClassCash().

When I open the report I get Runtime Error 2001 - You cancelled the previous operation. Funny thing is, if I remove the 'And Method=Cash' from the Function, it works fine and works out the sum of all payments on a given date.

Does anybody know how to get this function working?

I have tried changing the Table Design to just Text for the Method field removing the Value List but still get Error 2001.

 

[dkinley]

It's the quotes and reference to the form control, might try ...

"ToPay= 'No' AND Method= 'Cash' AND DateUpdated= " & Forms!ReportDates!txtFrom), 0)

-dK

 

[NTF73]

Nearly There! I don't get the error now but the value returns Null

 

[NTF73]

This on works

"ToPay=No AND Method= ""Cheque"" AND DateUpdated=Forms!ReportDates!txtFrom"), 0)

Thanks dkinley!

 

[dkinley]

Soz .. just got back to check - I was suspicious on the No bit, if Yes/No type or not and I forgot to put your Nz back on it ...

Glad it is working for you!

-dK