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
> 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:
[quoted text clipped - 8 lines]
> This only gives the correct answer for periods >6 & >9. Can anyone help
> please.
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.
> 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:
[quoted text clipped - 9 lines]
> This only gives the correct answer for periods >6 & >9. Can anyone help
> please.
Alanclen - 18 Apr 2008 14:06 GMT
> 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:
[quoted text clipped - 37 lines]
>
> Thanks very much for your help. All working fine.