Query not returning the correct results

[BBK]

I have 3 text boxes in a form

- Rent Amount is user input field
- Payment frequency is dropdown combo box with weekly rent or monthly rent
- Monthly Rent Due - is calculation field with this formula =[RentAmount]*Switch([PaymentFrequency]="Monthly Rent",1,[PaymentFrequency]="Weekly Rent",13/3

I am trying to write a query to sum the monthly rent due and give me an average of rent due for the month.
But when i bring the field MonthlyRentDue into my query it is giving me incorrect value back for any weekly rent (seems to multiply * 4) e.g I have one tenant paying $165 pw but it should be $715 per month as per formula above which is correct but when i do my query and run it giving a figure of $660 which is not correct.

This is sql code for my query

SELECT tblLease.LeaseID, tblLease.PaymentFrequency, tblLease.MonthlyRentDue
FROM tblLease;

Any help is really appreciated as I have spent all day trying to figure it out.

Thanks

 

[CraigDolphin]

Not sure what you're doing to cause that issue as the expression works fine for me. I can't really debug your situation without seeing the database itself but here's a quick and dirt database I whipped up showing the code working just fine. Maybe you can figure out what you're doing wrong by comparing it with yours.

 

[BBK]

Craig thank you for time and assistance.

I tried to change my DB to match your settings but it still miss calculating the weekly amount

I attached the DB with test info, so if you get some spare time i would appreciate if you could take a quick look.

If you go to 'New Lease Details Form' - (LeaseID 6) This is where the problem is occuring or in the query 'Display Rent Rate'

Thanks again for the time and help

 

[CraigDolphin]

In your db, the text box control that is bound to the RentAmount field is named 'MonthlyRent', whereas in my example all the code refers to a textbox control named 'RentAmount'. If you change the control name accordingly, it should all work. Once you've made the change, re-enter the weekly rental value and it should recalculate the correct value.

At this point, though, I'd be remiss if I didn't point out that, generally speaking, you really shouldn't store a calculated value like this in a table. Instead, you can simply calculate it whenever you like based on the supporting data that exists in the table.

For example, if you want to see this calculation at work in a query output then you could do something like:

SELECT tblLease.RentAmount, tblLease.PaymentFrequency, [RentAmount]*Switch([PaymentFrequency]="Monthly Rent",1,[PaymentFrequency]="Weekly Rent",13/3) AS CalculatedMonthlyAmount
FROM tblLease;

Or if you want to see it in a control on a form, then you'd simply use an unbound control and use the expression as the Control Source for that control.

This kind of approach saves you valuable file space, and means that all values you see are always calculated using the most up-to-date information.

 

[BBK]

Thanks for that I got it working and thanks for advice as well.