Display a result in a textbox depending on two other criteria

[BBK]

I have 3 input fields:

- RentAmount ( Currency, entered by user )
- PaymentFrequency ( Drop down selection which is either Weekly or Monthly )
- MonthlyAmountDue ( Currency, calculated )


I would like to be able to have the 'MonthlyAmountDue' textbox automatically display the total Rent Amount.

The problem i have is:
If PaymentFrequency is weekly then i need to multiply the 'RentAmount by 4' and display this result then in the 'MonthlyAmountDue' textbox

If PaymentFrequency is monthly then do nothing and just display this result in the 'MonthlyAmountDue' textbox.

Stupid as this is, i dont know how to go about doing it, or how to know if the PaymentFrequency is Monthly or Weekly so wether or not to multiply by 4 or not.

Any help or advice would be greatly appreciated.
Thanks for reading

 

[SteveSchapel]

BBK,

Set the Control Source property of the MonthlyAmountDue textbox to like this:

=[RentAmount]*Switch([PaymentFrequency]="Monthly",1,[PaymentFrequency]="Weekly",4)

So then, the only question I would have is... Is it correct in your circumstances that monthly amount = weekly amount * 4? In my experience, monthly means calendar monthly, which is not equivalent to 4 weeks. Just a thought...

 

[BBK]

Uh.... never even thought of that problem.

Any idea or is it even possible to overcome this easily... or is it a complicated process to solve this issue

Thanks for your time Steve and assistance

 

[Brianwarnock]

Wont the original rents have been set to allow for this so that the annual rent is either monthly*12 or weekly*52 and the real problem is in the labelling?

I have an annual pension that is paid 4 weekly, which means that some months a get paid twice, probably only happens once, 4 weekly is not an unusual occerence but should not be labelled monthly.

Brian

 

[SteveSchapel]

BBK,

I guess it depends on the reason for wanting to calculate a monthly amount, and what you want to do with these figures. If you have 52 weekly rental payments in a year, then [RentAmount]*52/12 will give you the average amount per calendar month. So, as long as this is underatood to be an average, and not reflect a precise amount that you would actually see get paid in any given month, then I guess you can modify my earlier suggested formula to:

=[RentAmount]*Switch([PaymentFrequency]="Monthly",1,[PaymentFrequency]="Weekly",13/3)

 

[DCrake]

As Steve said rent is usually paid per calendar month and is documented in the tenancy agreement. Tenants usually like to know in adance what their rent will be each month, not have to sit and calculate it based on how many weeks there are in a month. If the Aggreement states weekly rent then this will be fixed at the outset. Likewise for calandar months the rent is preset.

 

[BBK]

Hi

Thanks for all your replies and help, it is greatly appreciated. The formula below works for our requirements but i would like it dispaly a currency value. I did change the properties (format) to currency but its still not displaying it as a currency just a figure.

=[RentAmount]*Switch([PaymentFrequency]="Monthly Rent",1,[PaymentFrequency]="Weekly Rent",52/12)