Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / April 2008

Tip: Looking for answers? Try searching our database.

Calculations in Access

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Alanclen - 18 Apr 2008 10:58 GMT
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.
scubadiver - 18 Apr 2008 11:55 GMT
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.
BruceM - 18 Apr 2008 12:55 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:
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.