Results 1 to 14 of 14
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: If then else elseif

    I have tried multiple ways of writing this but I am just not getting the code write. Hopefully you will be able to set me straight. I am using Access 2003.

    This is the code which has been entered. I hope you can see what I am trying to do but it is just not working:

    Private Sub InactiveReason_AfterUpdate()
    If Not IsNull(Me.StaffActInact) Then
    MsgBox "Change record to INACTIVE"

    ElseIf IsNull(Me.StaffActInact) Then
    MsgBox "Change record to ACTIVE"

    ElseIf Me.StaffActInact = "" Then
    MsgBox "Change record to ACTIVE"

    Else
    If Me.StaffActInact = " " Then
    MsgBox "Change record to ACTIVE"
    End If
    End Sub

  2. #2
    Join Date
    May 2002
    Posts
    157
    Oops I feel so dumb!!

    It does help if you refer to the correct field name.

    The following ended up working for me:


    Private Sub InactiveReason_AfterUpdate()

    If IsNull(Me.InactiveReason) Then
    MsgBox "You may need to change the record to ACTIVE"

    ElseIf Me.InactiveReason = "" Then
    MsgBox "You may need to change the record to ACTIVE"

    ElseIf Me.InactiveReason = " " Then
    MsgBox "You may need to change the record to ACTIVE"
    Me.InactiveReason = Null

    ElseIf Not IsNull(Me.InactiveReason) Then
    MsgBox "You may need to change the record to INACTIVE"

    End If

    End Sub

  3. #3
    Join Date
    Jun 2014
    Posts
    8

    Need Help

    Please Help me...

    I am using MS Access 2007 & I want to add some calculations on my database.

    1st Q: Field 2 is /20 than add ans in Field 1 if Field 2 is <20 it remain the same no changes.

    2nd Q: Field 3/9 then add ans in Field 2 if Field 2 is <9 it remain the same.

    Please Help me regarding this.

  4. #4
    Join Date
    May 2002
    Posts
    157
    Hi mwaleedgul - you just replied to a thread I have subscribed to entitled - If then else elseif - in the Microsoft Access forum of dBforums.

    Here is the message that has was posted:
    ***************
    Please Help me...

    I am using MS Access 2007 & I want to add some calculations on my database.

    1st Q: Field 2 is /20 than add ans in Field 1 if Field 2 is <20 it remain the same no changes.

    2nd Q: Field 3/9 then add ans in Field 2 if Field 2 is <9 it remain the same.

    Please Help me regarding this.
    ***************

    Hi mwaleedgul before I can really assist you I need to know exactly how you want to calculate the above.
    I have created a table called Table1 I have also created a Query called Query1.
    Table 1 has two fields: Field_1 and Field_2

    Open the Query1 and change the view to sql view, then paste the following in:
    SELECT Table1.Field_1, Table1.field_2, [field_2]/20 AS Expr1, IIf([field_2]/20<20,[field_2]/20,([field_2]/20)+[field_1]) AS Expr2
    FROM Table1;

    Then change the view back to datasheet view. Is this the sort of answer you are looking for?
    - If no then when you reply try to give examples so I can understand how you are trying to calculate this.
    - Are you trying to keep the answer permanently or do you only need to display it.
    - Do you want the answer as a query or is it to be part of the coding for a form?

  5. #5
    Join Date
    May 2002
    Posts
    157
    sorry created in error
    Last edited by Karen Day; 06-24-14 at 11:31.

  6. #6
    Join Date
    Jun 2014
    Posts
    8

    Need Help.

    Many Thanks for replying

    I have table. Reports Issued for Verification
    I have Query. MouzaSummary-JV

    I have 3 Fields in table. Kanal Marla & Sarsai

    Now I want below calculations on report. On Report I want to display things like this.

    Marla/20 than add ans in Kanal if Marla<20 it remain the same no changes.

    Sarsai/9 then add ans in Marla if Sarsai<9 it remain the same.

    I hope you get my point. Please help. Thanks.

  7. #7
    Join Date
    May 2002
    Posts
    157

    If then else elseif

    Hi mwaleedgul -
    So you are saying:
    Part 1
    1. Get the figure in Marla and divide it by 20 (so if the figure in Marla is 1000 and you divide it by 20 the answer is 50)
    2. Now get the new calculated figure in point 1 and check if the figure is less than 20. If it is less than 20 keep the answer as is, no change (ie Marla Divided by 20). If the figure is 20 or more then go to point 3.
    3. Now get the new calculated figure in point 1 and check if the figure is equal to or greater than 20. If it is equal to or greater than 20 then get the figure calculated in point 1 (ie Marla divided by 20) and add to it the figure in Kanal.

    Part 2
    4. Get the figure in Sarsai and divide it by 9 (so if the figure in Sarsai is 150 and you divide it by 9 the answer is 55.5555)
    5. Now get the new calculated figure in point 4 and check if the figure is less than 9. If it is less than 9 keep the answer as is, no change (ie Sarsai Divided by 9). If the figure is 9 or more then go to point 6.
    6. Now get the new calculated figure in point 4 and check if the figure is equal to or greater than 9. If it is equal to or greater than 9 then get the figure calculated in point 4 (ie Sarsai divided by 9) and add to it the figure in Marla.


    Eg Part 1
    Marla has a figure of 1000. Kanal has a figure of 2000. And Sarsai has a figure of 500.
    1000 / 20 = 50. 50 is not less than 20. So answer is no change and = 50
    But if Marla has a figure of 300. Kanal has a figure of 600. And Sarsai has a figure of 150.
    300 / 20 = 15. 15 is less than 20. So the answer is 15 + 600 = 615.

    Eg Part 2

    Marla has a figure of 1000. Kanal has a figure of 2000. And Sarsai has a figure of 500.
    500 / 9 = 50. 55.5555 is not less than 9. So answer is no change and = 55.5555
    But if Marla has a figure of 150. Kanal has a figure of 300. And Sarsai has a figure of 75.
    75 / 9 = 8.3333. 8.3333 is less than 9. So the answer is 8.3333 + 150 = 158.3333.


    I have created a table called Table1 I have also created a Query called Query1.
    Table 1 has three fields: Kanal Marla & Sarsai

    Open the Query1 and change the view to sql view, then paste the following in:
    SELECT Table1.Kanal, Table1.Marla, Table1.Sarsai, [Marla]/20 AS Expr1, IIf([Marla]/20<20,[Marla]/20,([Marla]/20)+[Kanal]) AS Expr2, [Sarsai]/9 AS Expr3, IIf([Sarsai]/9<9,[Sarsai]/9,([Sarsai]/9)+[Marla]) AS Expr4
    FROM Table1;

    Then save and change the view back to datasheet view. Is this the sort of answer you are looking for?

    All you then need to do is create a report using this query.

  8. #8
    Join Date
    Jun 2014
    Posts
    8

    Need little more help please.

    Thanks for helping me. its working I also need the summary of these calculations.

    SumOfMarla/20 than add ans in SumOfKanal if SumOfMarla<20 it remain the same no changes.

    SumOfSarsai/9 then add ans in SumOfMarla if SumOfSarsai<9 it remain the same.

    I will be very thankful to you please help me...

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so define a report footer and SUMarise those values in that section
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2014
    Posts
    8
    Thanks for reply but I need summary like below.

    S. No. Mouza SumofKanal SumofMarla SumofSarsai
    1 Mouza1 100 19(<20) 8(<9)
    2 Mouza1 100 19(<20) 8(<9)
    3 Mouza1 100 19(<20) 8(<9)
    4 Mouza1 100 19(<20) 8(<9)
    5 Mouza1 100 19(<20) 8(<9)
    6 Mouza1 100 19(<20) 8(<9)

    Note: SumofMarla should be less then <20 and sumofsarsai should be less then 9
    Please guide me once more
    Thanks.

  11. #11
    Join Date
    May 2002
    Posts
    157
    Hi there if you want to incorporate rounding to 2 decimal positions then do the following:

    Open the Query1 and change the view to sql view, then paste the following in:

    SELECT Table1.Kanal, Table1.Marla, Table1.Sarsai, Round([Marla]/20,2) AS Expr1, IIf([Marla]/20<20,Round(([Marla]/20),2),Round((([Marla]/20)+[Kanal]),2)) AS Expr2, Round([Sarsai]/9,2) AS Expr3, IIf([Sarsai]/9<9,Round([Sarsai]/9,2),Round((([Sarsai]/9)+[Marla]),2)) AS Expr4
    FROM Table1;

  12. #12
    Join Date
    May 2002
    Posts
    157
    Regarding summing your report
    1. create your report showing each query field you want to show.
    the query I got you to create has fields called Kanal Marla Sarsai Expr1 Expr2 Expr3 Expr4
    2. in the Report Footer add in sum fields
    =Sum([Kanal]) =Sum([Marla]) =Sum([Sarsai]) =Sum([Expr1]) =Sum([Expr2]) =Sum([Expr3]) =Sum([Expr4])

    I hope this helps

  13. #13
    Join Date
    Jun 2014
    Posts
    8

    More Help need

    Please Help....

    Please see the picture link is given below.
    https://www.dropbox.com/s/vfc8y845vnk9jle/Summary.jpg

    I Want the same thing in summary.

    Marla divided by 20 and ans add in the sumofkanal
    (For example)
    Kanal field have value = 3525
    Marla field have value = 936
    Sarsai field have value = 17

    (Note: 20 Marla = 1 Kanal and 9 Sarsai = 1 Marla)

    I want.... marla always be below 20 and if its 936 is should be divide by 20 and ans 46.8 is add in Kanal field (Note: .8 = 16 Marla and It is <20 so .8 should be display in Marla (.8 mean 16 Marla) and 46 should add in the Kanal. same procedure for sarsai.

    Some Examples of decimals values in Marla
    .05 = 1 Marla
    .1 = 2 Marla
    .2 = 4 Marla
    .3 = 6 Marla
    .4 = 8 Marla
    .5 = 10 Marla
    .6 = 12 Marla
    .7 = 14 Marla
    .8 = 16 Marla
    .9 = 18 Marla
    100 = 20 Marla

    I hope you get my point now...

  14. #14
    Join Date
    May 2002
    Posts
    157
    Hi mwaleedgul
    I am sorry but not even the JPG you pointed me to is helping me to understand what you are trying to do.
    Perhaps someone else in the forum can help?



    Here is the message that has just been posted:
    ***************
    *Please Help....*

    Please see the picture link is given below.
    https://www.dropbox.com/s/vfc8y845vnk9jle/Summary.jpg

    I Want the same thing in summary.

    Marla divided by 20 and ans add in the sumofkanal (For example) Kanal field have value = 3525 Marla field have value = 936 Sarsai field have value = 17

    (Note: 20 Marla = 1 Kanal and 9 Sarsai = 1 Marla)

    I want.... marla always be below 20 and if its 936 is should be divide by 20 and ans 46.8 is add in Kanal field (Note: .8 = 16 Marla and It is <20 so .8 should be display in Marla (.8 mean 16 Marla) and 46 should add in the Kanal. same procedure for sarsai.

    Some Examples of decimals values in Marla
    .05 = 1 Marla
    .1 = 2 Marla
    .2 = 4 Marla
    .3 = 6 Marla
    .4 = 8 Marla
    .5 = 10 Marla
    .6 = 12 Marla
    .7 = 14 Marla
    .8 = 16 Marla
    .9 = 18 Marla
    100 = 20 Marla

    I hope you get my point now...
    ***************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •