Quote:
Originally Posted by dozingquinn
Hello,
I'm new to the world of VBA & Access (2000), so I really don't know that much. I am trying to setup a VBA multiple if statement for a new field and would really appreciate some help and pointers in the right direction.
Essentially, the statement is designed to look up the payment frequency entered, then divide and multiply the expense field accordingly.
Is this the best way of going about it?
If [frequency] = "1 week" then
([expense] / 7 ) * 30.4
Elseif [frequency] = "2 weeks" then
([expense] / 14 ) * 30.4
Elseif [frequency] = "3 weeks" then
([expense] / 21 ) * 30.4
Elseif [frequency] = "4 weeks" then
([expense] / 28 ) * 30.4
Elseif [frequency] = "1 month" then
[expense]
Elseif [frequency] = "2 months" then
[expense] / 2
Elseif [frequency] = "3 months" then
[expense] / 3
Elseif [frequency] = "6 months" then
[expense] / 6
Elseif [frequency] = "12 months" then
[expense] / 12
Elseif [frequency] = "24 months" then
[expense] / 24
End If
Thankyou!
Hi
That would do what you want (I assume), but I tend to use Select Case constuct for this type of thing.
However, as I do not know what/where precisely you are using this, but I am guessing that the suggestion below is valid!!
It may be better to create a lookup table with fields like this
- ID Frequency Factor
-
1 1 Week 0.2303
-
2 2 Weeks 0.4605
-
3 3 Weeks 0.6908
-
4 4 Weeks 0.9210
-
5 1 Month 1
-
6 2 Months 2
-
etc
You can the use this to enter the frequency ID into the table with the expenses using as combo control.
Then you can calculate the monthly expenses for each expense directly by dividing the expeses by the Factor in a query for that record as they are returned ?
Does that make any sense?
This could be total tosh, but I assume the Frequency (now the ID!) is entered somewhere so the factor relating to it will always be known/available with this method.
??
MTB
