DSUM or ????

[kvest]

Hello,
I have a form DailyDrawRecon that is based on a qry (DailyRevenue) that I am creating to get rid of an excel spreadsheet used to reconcile cash drawers at the end of the buisness day. The qry prompts the user for the buisness date and the drawer user name prior to generating the form. I am almost done, however, I cannot figure out how to sum and count the transactions by Payment Type (cash, money order, credit card, ATM)? I have used Count and Sum before, but only to total all the records.

Can I put the calculation box on the form header? The form detail section lists all transactions in receipt order.

The types are as follows:

PaymentDate - Date/Time
PaymentType - Text
Amount Paid - Currency

From what I have read, DSUM and DCOUNT are what I need, but I am not getting very far with them. The transactions are stored in tbl MoneyCard.

Help ! and Thanks

 

[kvest]

I got it working, but it displays that total sum of all "Money Order" payments that are in the MoneyCard table. I was afraid of this and expected this outcome. How can I limit the DSUM to just what transactions are in the query? When I try and Sum the Payment Type in the query, I get an error "To complex an expression to be evaulated".

I am completly lost now....

 

[edsuk]

The domain for DSum includes queries, therefore modify the DSum as follows:

DSum("[Money Order]","DailyRevenue")

This will give you the sum for "Money Order" within the result set of the query.

HTH

Mark...

 

[kvest]

Thanks....I now am getting #Name.

 

[kvest]

Mark,
This is what I have in the control source for this unbound box on the form.

DSum("[AmountPaid]", "[qry DailyRevenue]", "[PaymentType] = 'Money Order'")

I have checked the names on all of these fields and they match the tables or query. The only thing I havent done is rename my query to get rid of that space.

I am still getting #Name. AARRGGHH

 

[wtmckown]

Add an = sign to the control source property. =DSum("[AmountPaid]", "[qry DailyRevenue]", "[PaymentType] = 'Money Order'")

 

[kvest]

wtmckown,
I tried that also...now I get an #error. Does it matter where this box is on the form or what type box it is? I am about ready to give up and start over by using a subform. I dont know if that will help or not, but this way isnt working.

Thanks...

 

[PHV]

The form is a continuous form based on [qry DailyRevenue] ?
In the footer section create a textbox with control source set to:
=Sum(IIf([PaymentType]='Money Order', [AmountPaid], 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kvest]

PHV,
That did it!!! Thanks a ton. I would prefer it up higher on the form, but can live with this as long as it works.

Can you refer me to something so I can learn why this is?

Thanks again.