Sum a field based on a certain month?!?

[superhorse]

Hi

I was wondering how easy it is to sum a field based on a certain month.

To explain further, at the moment I have a form that does several calculations. One of which uses the sum function to calculate the total of a cost field in a different form. I would now like to change it so that instead of calculating the whole total, it just calculates the total cost for the current month.

On the form that has the cost I have the date for each record in the format dd/mm/yyyy

On the form that the calculation is being worked out on I have a date value which defaults as the current date in the format dd/mm/yyyy

I am hoping that there is some sort of code that when I open my calculations form it will look at todays date and match up the month and the year with the month and the year in the cost field of the second form and then sum each of the values, giving me the total cost for the current month.

Hopefully there are a few brainboxes out there that can help me with this one. Hopefully I didn't confuse you too much.

Cheers.

 

[rainman89]

you can use

Between date1 AND date2

as criteria in your date field to return the dates between the 2 and then compute your total based on that

 

[superhorse]

Would that mean that I would have to type in the start and end date in manually to return that months data?

 

[rainman89]

no. you can pull this from the textboxes on your forms use this link

http://www.mvps.org/access/forms/frm0031.htm

to help you reference the forms

 

[Brianwarnock]

in the query create a field say mthyr: format([yourdatefield], "mm/yy")

and in the criteria put
Format(Date(),"mm/yy")

Brian

Edit except your not doing it in a query are you

 

[superhorse]

Thanks for the help. Although I don't understand how you use the code to match up the month and year once the fields are referenced.