Calculations in Access

A

Alanclen

I'm very new to Access and am having difficulty getting a calculation to
work. I have code in the AdultFare(After_Update) field as follows:
Private Sub AdultFare_AfterUpdate()

If [Period] < 5 Then [AdContractPrice] = [AdultFare] * 31.5
If [Period] > 4 < 7 Then [AdContractPrice] = [AdultFare] * [Period] * 7.65625
If [Period] > 6 < 10 Then [AdContractPrice] = [AdultFare] * [Period] * 7
If [Period] > 9 Then [AdContractPrice] = [AdultFare] * [Period] * 6.5625

End Sub
This only gives the correct answer for periods >6 & >9. Can anyone help
please.
 
S

scubadiver

I am not guaranteeing this is correct:

If [Period] < 5 Then [AdContractPrice] = [AdultFare] * 31.5
If [Period] > 4 and If [Period] < 7 Then [AdContractPrice] = [AdultFare] *
[Period] * 7.65625
If [Period] > 6 and If [Period] < 10 Then [AdContractPrice] = [AdultFare] *
[Period] * 7
If [Period] > 9 Then [AdContractPrice] = [AdultFare] * [Period] * 6.5625
 
B

BruceM

I will assume that Period is a whole number. Scubadiver is on the right
track, but the If doesn't appear twice. Also, I would use the Me syntax:
If Me.Period > 4 and Me.Period < 7 Then ...

Select Case may be simpler to write:

Select Case Me.Period
Case < 5
Me.AdContractPrice = Me.AdultFare * 31.5
Case < 7
Me.AdContractPrice = Me.AdultFare * Me.Period * 7.65625
Case < 10
Me.AdContractPrice = Me.AdultFare * Me.Period * 7
Case Else
Me.AdContractPrice = Me.AdultFare * Me.Period * 6.5625
End Select

As soon as a condition is met, Select Case ends. If Period is 6 it is not
less than 5, but it is less than 7, so the second case applies. No more
conditions are tested.

Using If statements as you have done (sequential rather than being nested),
it *is* necessary to check the > and < values each time, since each
condition will be tested every time the code runs. By the way, you can use
= and <=, which may make the code easier to follow.
Click to expand...


Alanclen said:
I'm very new to Access and am having difficulty getting a calculation to
work. I have code in the AdultFare(After_Update) field as follows:
Private Sub AdultFare_AfterUpdate()

If [Period] < 5 Then [AdContractPrice] = [AdultFare] * 31.5
If [Period] > 4 < 7 Then [AdContractPrice] = [AdultFare] * [Period] *
7.65625
If [Period] > 6 < 10 Then [AdContractPrice] = [AdultFare] * [Period] * 7
If [Period] > 9 Then [AdContractPrice] = [AdultFare] * [Period] * 6.5625

End Sub
This only gives the correct answer for periods >6 & >9. Can anyone help
please.
Click to expand...
 
A

Alanclen

BruceM said:
I will assume that Period is a whole number. Scubadiver is on the right
track, but the If doesn't appear twice. Also, I would use the Me syntax:
If Me.Period > 4 and Me.Period < 7 Then ...

Select Case may be simpler to write:

Select Case Me.Period
Case < 5
Me.AdContractPrice = Me.AdultFare * 31.5
Case < 7
Me.AdContractPrice = Me.AdultFare * Me.Period * 7.65625
Case < 10
Me.AdContractPrice = Me.AdultFare * Me.Period * 7
Case Else
Me.AdContractPrice = Me.AdultFare * Me.Period * 6.5625
End Select

As soon as a condition is met, Select Case ends. If Period is 6 it is not
less than 5, but it is less than 7, so the second case applies. No more
conditions are tested.

Using If statements as you have done (sequential rather than being nested),
it *is* necessary to check the > and < values each time, since each
condition will be tested every time the code runs. By the way, you can use
= and <=, which may make the code easier to follow.
Click to expand...


Alanclen said:
I'm very new to Access and am having difficulty getting a calculation to
work. I have code in the AdultFare(After_Update) field as follows:
Private Sub AdultFare_AfterUpdate()

If [Period] < 5 Then [AdContractPrice] = [AdultFare] * 31.5
If [Period] > 4 < 7 Then [AdContractPrice] = [AdultFare] * [Period] *
7.65625
If [Period] > 6 < 10 Then [AdContractPrice] = [AdultFare] * [Period] * 7
If [Period] > 9 Then [AdContractPrice] = [AdultFare] * [Period] * 6.5625

End Sub
This only gives the correct answer for periods >6 & >9. Can anyone help
please.
Click to expand...

Thanks very much for your help. All working fine.
Click to expand...
 
Top