Calculations in a form

[elmere]

I am a complete Access beginner, and recently I have been trying to use Access to keep a log of expenses for staff at work (total to date for each staff, and total for each staff in the last 6 months).



I have a sub form in data sheet view that shows expenses by a staff, linked to the main form by StaffID (auto number). I have a text box in the in the sub form that calculates the total expenses to date for each staff which works fine (using =Sum([Payment]) in the sub form and then using =[subfrmPayment].[Form]![SumOfPayment] in the main form).

However for total expenses for each staff in the last 6 months I have tried using =DSum("Payment","tblPayment",DateDiff('d',[PaymentDate],Date())<180) in the same sub form and then using =[subfrmPayment].[Form]![6MonthPayment] in the main form. Unfortunately it calculates the total expenses for all staff for the last 6 months (or roughly 180 days), rather than for each staff.

Given that I have little knowledge about Access, is it possible to achieve the above without using VBA code?

Any advice for the above will be greatly appreciated!

[baumannp]

I'm not an expert, but I'm pretty sure you can accomplish all that without VBA. I'd probably just filter the data by date in a query then send it to a report.

[elmere]

I'm not an expert, but I'm pretty sure you can accomplish all that without VBA. I'd probably just filter the data by date in a query then send it to a report.

Thanks baumannp.

I thought about perhaps a form based on a query, however it seems every time I need the latest total the query needs to be run (if I remember correctly this could be done by going to the next record in the main form and then going back to the original record). Whereas performing the calculations in the form (certainly for the total for each staff to date), any update in the sub form is seemingly automatically updated in the total to date field in the main form (=[subfrmPayment].[Form]![SumOfPayment]) without selecting the next record and going back to the original record in the main form.

Thats why I am sticking with the form based approach (unless there is something I am not aware of).

[elmere]

I have attached a copy of my database.

I am still having a problem with the '6 month payment' textbox: it calculates the total payment for all staff for the last 6 months, rather the total payment for each staff for the last 6 months.

Hopefully it will better illustrate what I am trying to achieve!

Any pointers or advice will be greatly appreciated.