SUM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello i have a query set up but its pulling all the records.

One filed gets a total $amount and the second gets a count of all payments.
what i need is to get a count of those only with a payment amount. also i
want to be able to select a start date and end date to query.
 
To count only actual payments, modify your current totals query by adding
the criteria "Is Not Null" in the amount column. To filter by a date range
add the criteria "Between [Enter START Date] and [Enter END Date]" in the
payment date column.
-Ed
 
Bronson,

For your date range, you should make a form (or use an existing form
that will always be open at the time that the query will be called
upon), with a couple of unbound textboxes, let's say you name them
DateFrom andf DateTo. Then, in the Criteria of the date field in your
query, put this...
Between [Forms]![NameOfForm]![DateFrom] And [Forms]![NameOfForm]![DateTo]

As regards count of payments, I am not 100% sure what you mean, so if I
miss the point, maybe you can post back with some examples and also the
SQL view of the query as you currently have it. But if I undrerstand
you correctly, you should put this in the Field row of the query design...
CountOfPayments: -Sum([PaymentAmount] Is Not Null)
 
I got both to work thank you both.

Steve Schapel said:
Bronson,

For your date range, you should make a form (or use an existing form
that will always be open at the time that the query will be called
upon), with a couple of unbound textboxes, let's say you name them
DateFrom andf DateTo. Then, in the Criteria of the date field in your
query, put this...
Between [Forms]![NameOfForm]![DateFrom] And [Forms]![NameOfForm]![DateTo]

As regards count of payments, I am not 100% sure what you mean, so if I
miss the point, maybe you can post back with some examples and also the
SQL view of the query as you currently have it. But if I undrerstand
you correctly, you should put this in the Field row of the query design...
CountOfPayments: -Sum([PaymentAmount] Is Not Null)

--
Steve Schapel, Microsoft Access MVP

Hello i have a query set up but its pulling all the records.

One filed gets a total $amount and the second gets a count of all payments.
what i need is to get a count of those only with a payment amount. also i
want to be able to select a start date and end date to query.
Click to expand...
Click to expand...
 
Back
Top