Query and total payment field based on date field.

[NicoleOB]

I am working on a database for an organization that wants to track contacts & donations for each contact. I have a form for donations with a subform that displays Donation Amount, Date, Payment Method etc. for the current contact I would like to display on the main form the total donations for the current year. Also, I would like to do a report to display all contacts who have donated more than X dollars within a specific year.

Thank you!

 

[LonnieJohnson]

On the first part you can create an unbound text box that uses the DSum function as a ControlSource. Here you can filter the year. If you are not familiar with the DSum function as a ControlSource, let us know your table name and the fields in question and we can write the statement.

On the second issue it's just a matter of creating a select query that picks records that have a dollar amount greater than X. To get the current year put the following in the criteria section for the Year in question...
Like "*" & Format(Now(),"yyyy&quot

Hope some of this helps.


prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[NicoleOB]

Lonnie - thanks so much. I am familiar with the DSum function enough to select the field (PaymentAmount) from my table [tblDonations] - however it's the criteria part that I'm having a hard time with now. First - how do I apply the filter for the year - is this part of the DSum statement? And I also want to enter criteria so that it will total only the donations for the current contact (identified by [MemberID]) which is on the current form and in the table [tblDonations].

Thanks!

 

[LonnieJohnson]

DSum ("PaymentAmount","tblDonations","MemberID = '" &
Me!MemberID & "' And MyDateField Like '*' &
Format(Now(),"yyyy&quot)"

If your MemberID is a numeric field you will need to remove the apostrophe around this part...

"MemberID = " & Me!MemberID & " And...

Hope this helps.
prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.