SUM

B

Bronson

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.
 
E

Ed Robichaud

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
 
S

Steve Schapel

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)
 
B

Bronson

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...
 
Top