Solved Exercises for if elseif else ... (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:39
Joined
Feb 28, 2001
Messages
26,996
but how i have to create JOIN to a translation table?
A translation JOIN looks SOMETHING like this:

PersonTable: PersID, PersFirst, PersMid, PersLast, Addr1, Addr2, City, StateAbbrev
PersID is the primary key and StateAbbrev is the 2-letter postal abbreviation.

StateTable: StateAbb, StateLong
StateAbb is the primary key (natural, not a synthetic key) and example records might be "LA, Louisiana" or "KS, Kansas" or "TX, Texas"

Then

Code:
SELECT P.PersID, P.PersFirst, P.PersMid, P.PersLast, P.Addr1, P.Addr2, P.City, S.StateLong
FROM PersonTable P INNER JOIN StateTable S ON P.StateAbbrev = S.StateAbb ...

This would use the state abbreviation in the PersonTable to look up the state's long name from the StateAbbrev table using the method of a table JOIN to accomplish the lookup. The above is a VERY SIMPLE translation lookup JOIN and does not include safeguards for bad data.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:39
Joined
May 21, 2018
Messages
8,463
ElseIf is a variation of the Else statement that originated prior to the Case statement. If you have something that can be implemented with a Case, in the OLDEN days, prior to the advent of the Case statement, the ElseIF was a more compact way of expressing the conditions because it didn't require indentation and allowed you to code something that was mutually exclusive rather than dependent in a more visually pleasing way.

Now that we have the Case statement, ElseIf has outlived its usefulness. Use the Case for mutually exclusive conditions. Use the nested If for dependent conditions. And always pay attention to indentation so that dependencies are visible.
A select case and else if do not replace each other. A select case only evaluates a single expression and then checks the potential cases for that expression. An else if can check many expressions.

INI:
If Velocity > 100 Then
  do something if velocity > 100
elseif location = "South"
  do something if velocity <= 100 and in the South
elseif  Color <> "Red"
  do something if <= 100 and <> South and not Red
else
  do something if <= 100, not in South, Red
end if

The above cannot be written as a Select Case nor can it be written as multiple If Then.
Code:
if Velocity > 100 Then
  do something if velocity > 100
end if
if location = "South"
  do something if velocity <= 100 and in the South
end if
if  Color <> "Red"
  do something if <= 100 and <> South and not Red
end if
In the above event if the velocity is >100 it still will execute each subsequent check. You can do this with just If then else, but you would have to have much longer nested code. An else if is a shorter way of doing nested if thens not a select case alternative.
 
Last edited:

Grumm

Registered User.
Local time
Today, 13:39
Joined
Oct 9, 2015
Messages
395
A select case and else if do not replace each other. A select case only evaluates a single expression and then checks the potential cases for that expression. An else if can check many expressions.

INI:
If Velocity > 100 Then
  do something if velocity > 100
elseif location = "South"
  do something if velocity <= 100 and in the South
elseif  Color <> "Red"
  do something if <= 100 and <> South and not Red
else
  do something if <= 100, not in South, Red
end if

The above cannot be written as a Select Case nor can it be written as multiple If Then.
Code:
if Velocity > 100 Then
  do something if velocity > 100
end if
if location = "South"
  do something if velocity <= 100 and in the South
end if
if  Color <> "Red"
  do something if <= 100 and <> South and not Red
end if
In the above event if the velocity is >100 it still will execute each subsequent check. You can do this with just If then else, but you would have to have much longer nested code. An else if is a shorter way of doing nested if thens not a select case alternative.

Agree with this, but why in any professional code, you would want to do that ?
Why is a location depending of the velocity ?
(I know it is an example)
If you have have different expressions to check, split them and make them easier to read. That is my opinion of course ;)
 

cheekybuddha

AWF VIP
Local time
Today, 12:39
Joined
Jul 21, 2014
Messages
2,237
If you have to check 8 statments how would you start?
1. if
2. elseif
3.elseif
4.elseif
5. elseif
6.elseif
7.elseif
8.elseif
9. end if?
I dont know but this elseif is making me probelms when reading all my disaster...
Using Select Case ... End Select - 2 ways:

Code:
Private Sub NamenUporabe_Click()

  Dim criteria1 As String
  Dim criteria2 As String
  Dim criteria3 As String
  Dim criteria4 As String

  Select Case Me.NamenUporabe
    Case "TestnaAnaliza"
      Me.SproscanjeUstrezno = "N/A"
    Case Me.NamenUporabe = "SpreminjanjeStatusa"
      Me.SproscanjeUstrezno = "N/A"
      Me.OznakaInstrumenta = "N/A"
      Me.AnalitskiPostopek1 = "N/A"
      Me.AnalitskiPostopek2 = "N/A"
      Me.Analiza1 = "N/A"
      Me.Analiza2 = "N/A"
      Me.EmpChromChemMapa = "N/A"
      Me.RefNaAnalizo = "N/A"
      Me.StranStevilka = "N/A"
      Me.RaztopinaIzpiranje = "N/A"
      Me.CasIzpiranja = "N/A"
      Me.SteviloInjiciranj = "0"
      Me.AnalizaUstreza = "N/A"
    Case "RednaAnaliza"
      criteria1 = "[SproscanjeUstrezno] = 'DA' AND [KID] = " & Me.KID
      criteria2 = "[SproscanjeUstrezno] = 'DA' AND [KID] = " & Me.KID
      criteria3 = "[status] = 'approved' AND [KID] = " & Me.KID
      criteria4 = "[SproscanjeUstrezno] = 'DA' AND [KID] = " & Me.KID
      If DCount("*", "tblUporabaKolon", criteria1) = 0 Then
        MsgBox "Sproscanje ni bilo izvedeno.Prosim izvedite analizo sprošcanja.Ta zahteva ni mogoca in bo zavrnjena."
        Me.Undo
        DoCmd.Close acForm, "frmKoloneUporabaSUB"
      End If
    Case "Sprošcanje"
      If DCount("*", "tblUporabaKolon", criteria2) > 0 Then
        MsgBox "Ponovno želite izvajati analizo sprošcanja, ceprav je bilo Sprošcanje že ustrezno. Ta zahteva ni mogoca in bo zavrnjena."
        Me.Undo
        DoCmd.Close acForm, "frmKoloneUporabaSUB"
      End If
    End If
  End Select

End Sub

Private Sub NamenUporabe_BeforeUpdate(Cancel As Integer)

  Dim criteria3 As String
  Dim criteria4 As String

  Select Case True
  Case Me.StanjeSproscanja = "(3) Sproscena" And Me.NamenUporabe = "TestnaAnaliza"
    Me.StatusKolone = "(3) Sproscena"
    Me.SproscanjeUstrezno = "DA"
    Me.StatusKolone.Enabled = False
    Me.SproscanjeUstrezno.Enabled = False
  Case Me.StanjeSproscanja = "(3) Sproscena" And Me.NamenUporabe = "SpreminjanjeStatusa"
    Me.SproscanjeUstrezno = "DA"
    Me.OznakaInstrumenta = "N/A"
    Me.AnalitskiPostopek1 = "N/A"
    Me.AnalitskiPostopek2 = "N/A"
    Me.Analiza1 = "N/A"
    Me.Analiza2 = "N/A"
    Me.EmpChromChemMapa = "N/A"
    Me.RefNaAnalizo = "N/A"
    Me.StranStevilka = "N/A"
    Me.RaztopinaIzpiranje = "N/A"
    Me.CasIzpiranja = "N/A"
    Me.SteviloInjiciranj = "0"
    Me.AnalizaUstreza = "N/A"
  Case Me.StanjeSproscanja = "(3) Sproscena" And Me.NamenUporabe = "RednaAnaliza"
    criteria3 = "[status] = 'approved' AND [KID] = " & Me.KID
    criteria4 = "[SproscanjeUstrezno] = 'DA' AND [KID] = " & Me.KID
    If DCount("*", "tblKolone2", criteria3) > 0 And DCount("*", "tblUporabaKolon", criteria4) > 0 Then
      Me.StatusKolone = "(3) Sproscena"
      Me.SproscanjeUstrezno = "DA"
      Me.StatusKolone.Enabled = False
      Me.SproscanjeUstrezno.Enabled = False
      MsgBox "Analiza Sproščanja je bila izvedena vendar ni podpisana.Prosim podpišite analizo Sproščanja.Vaša zahteva bo zavrnjena."
      DoCmd.Close acForm, "frmKoloneUporabaSUB"
    End If
  End Select

End Sub
 

cheekybuddha

AWF VIP
Local time
Today, 12:39
Joined
Jul 21, 2014
Messages
2,237
BeforeUpdate version can also be:
Code:
Private Sub NamenUporabe_BeforeUpdate(Cancel As Integer)

  Dim criteria3 As String
  Dim criteria4 As String

  If Me.StanjeSproscanja = "(3) Sproscena" Then
    Select Case Me.NamenUporabe
    Case "TestnaAnaliza"
      Me.StatusKolone = "(3) Sproscena"
      Me.SproscanjeUstrezno = "DA"
      Me.StatusKolone.Enabled = False
      Me.SproscanjeUstrezno.Enabled = False
    Case "SpreminjanjeStatusa"
      Me.SproscanjeUstrezno = "DA"
      Me.OznakaInstrumenta = "N/A"
      Me.AnalitskiPostopek1 = "N/A"
      Me.AnalitskiPostopek2 = "N/A"
      Me.Analiza1 = "N/A"
      Me.Analiza2 = "N/A"
      Me.EmpChromChemMapa = "N/A"
      Me.RefNaAnalizo = "N/A"
      Me.StranStevilka = "N/A"
      Me.RaztopinaIzpiranje = "N/A"
      Me.CasIzpiranja = "N/A"
      Me.SteviloInjiciranj = "0"
      Me.AnalizaUstreza = "N/A"
    Case "RednaAnaliza"
      criteria3 = "[status] = 'approved' AND [KID] = " & Me.KID
      criteria4 = "[SproscanjeUstrezno] = 'DA' AND [KID] = " & Me.KID
      If DCount("*", "tblKolone2", criteria3) > 0 And DCount("*", "tblUporabaKolon", criteria4) > 0 Then
        Me.StatusKolone = "(3) Sproscena"
        Me.SproscanjeUstrezno = "DA"
        Me.StatusKolone.Enabled = False
        Me.SproscanjeUstrezno.Enabled = False
        MsgBox "Analiza Sproščanja je bila izvedena vendar ni podpisana.Prosim podpišite analizo Sproščanja.Vaša zahteva bo zavrnjena."
        DoCmd.Close acForm, "frmKoloneUporabaSUB"
      End If
    End Select
  End If

End Sub
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Sep 12, 2006
Messages
15,613
I don't use many elseif's if I can avoid it.
I use a case if I can see it's getting convoluted.

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
@MajP I think you missed my point about the ElseIF. It is an unnecessary construct. You can use a nested if (not independent if's as you depicted above) or if the IF's are mutually exclusive, you can use a Case statement.
Code:
If Velocity > 100 Then
  do something if velocity > 100
elseif location = "South"
  do something if velocity <= 100 and in the South
elseif  Color <> "Red"
  do something if <= 100 and <> South and not Red
else
  do something if <= 100, not in South, Red
end if
These If's are nested but that is not obvious from the indentation. You can code a Case statement as a nested if also. NEITHER are independent ifs and I never said they were. Your example of the left justified ElseIF is a visually informative way of representing a Case statement by using the IF statement rather than the Case statement. ElseIf visually confusing when the If's are dependent. There is no reason to ever use ElseIF. Use Case for mutually exclusive tests of a single field or use If-then-else for independent or nested conditions.
 
Last edited:

plog

Banishment Pending
Local time
Today, 07:39
Joined
May 11, 2011
Messages
11,611
Agree with Pat. ElseIfs make it easy to lose your logic, hard to implement it and easy to miss cases, especially when you are working with 2+ variables like in your velocity/color example.

So often you see people pose questions about 'why won't it ever hit this line of my if/else'. And it turns out its impossible to hit because prior cases catch it. Or they set one of the variables inside a prior if/else hoping a future if/else in the same block will catch it later on. Or they implement incomplete logic and the combination of all their variables never trigger any block of code or always hits their final else and they just can't find why.

If/elses can be rewritten with plain Ifs and if some blocks are code are to have precedence over others, you can order your ifs to achieve that or set a variable to denote that action has already been taken and no further action needed in further code.
 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
Yes I start to put inside elseif and then I didnt undertand nothing. Part of the code that I want was correct executed part of the code not and then I start thiking that I need more practice to undertand if- then- else statments. This is basic and is shame of me that I get into disaster.

Now I will try to implement my code too see if will work or not. For now I mamnged 80% working and then I get lost in strings...:rolleyes:
 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
Hi

cheekybuddha


I try to implement your help with the code but Is not working like I want.
I will look again to see if I have covered or possible conditions and I will try again to put select cases.
Maybe later if I managed something you can check again the code to see if I am in correct way.
Thank you

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
If-then-else is different from the ElseIF construct. We are telling you to not use ElseIF. If-then-else logic is easier to understand because it is interpreted the way we speak. If you learn to indent it properly so you can SEE the dependencies you will have a lot less trouble understanding how it works. The Case statement is even easier. If you want to check a field and do something different for more than two values, use a case. For two values, you can use an If.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
@lacampeona "not working" is not going to get you an answer. You need to be specific. Are you getting an error message? what is it? What are you expecting to happen? what is happening?
 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
Hi Pat
yes I will not use Elseif. I will try to combine if-then-else and select cases from now on .

With the code who show me cheekybudha
I get error run time error 94
Inavalid use of null error
Becouse the StanjeSproscanja is not having the status (3) Sproscena. It has another status in my case (6) Sending

this line

Case Me.StanjeSproscanja = "(3) Sproscena" And Me.NamenUporabe = "RednaAnaliza"


 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
ohh wrong the status of the item is Sending is not relased yet..and Me.StanjeSproscanja ( mean= if the item is already released). becouse the item is not already relased Me.StanjeSproscanja is empty field... and becouse of that access is saying me invalid use of null?
becouse I told him it must be relased and in my case is not relased yet...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:39
Joined
Feb 28, 2001
Messages
26,996
I don't think that kind of condition is legal in a CASE statement because it leaves no room for the implied comparison.

You use SELECT CASE variable followed by CASE comparison statements. You use the comparison statement as though the variable was in front of it. So you can have

Code:
SELECT CASE age
    CASE 0 to 12
...  execute if age between 0 and 12
    CASE 13 to 20
... execute if age between 13 and 20
    CASE 21
... execute if age = 21
    CASE > 21
... execute if age > 21
END SELECT

The problem with your statement is that you leave no meaningful place for the variable from the SELECT CASE statement to DO anything.

 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
Ohh yes I know I am so close and so far
I will think another possibility how to prevent that user will go to regualar analysis if the item is not released...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
Me.StatusKolone = "(3) Sproscena"
I'm going to guess that StatusKolone is numeric so you need to set it to the ID value not the string.

Also, why are you trying to set the values of two different fields to the same thing. You already have one field with a value of "(3) Sproscena". Why do you need StatusKolone to have the same value.

@The_Doc_Man Are we looking at the same code? I don't see anything structurally wrong with the posted code. Can you point to the error I am not seeing?
 

lacampeona

Registered User.
Local time
Today, 13:39
Joined
Dec 28, 2015
Messages
392
StatusKolone is text field...I just put the number in front becouse it was more fancy to the users? they say ohh if i see 3 Sproscena i will know in the second that this mean that this is relased,... :oops:

I could use ony Sproscena for the status item. Only released.

I am using here StatusKolone and StanjeSproscanja ( this is dolookup field which is cheking if the item is relased?) Maybe I should only use the statusKolone becouse StatusKolone and StanjeSproscanja is telling me the same no?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
I think we're loosing something in the translation. Please identify the exact line that is raising the error.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:39
Joined
May 21, 2018
Messages
8,463
Agree with this, but why in any professional code, you would want to do that ?
Why is a location depending of the velocity ?
(I know it is an example)
If you have have different expressions to check, split them and make them easier to read. That is my opinion of course ;)
If you read my post there is no recommendation to use an else if. The point was that a select case is not a replacement for else if in the cases where you are evaluating multiple expressions. It is shorthand for doing a nested if then else. I can do that example with nested if then else but not with a select case. I did not say that this is better or more readable then the nested if then else. I personally use elseif often, because i am very good at conditional logic, but would recommend nested ifs for the OP. Again only in cases where you are evaluating multiple expressions. Select case where you evaluate one expression with many conditions. Apples and oranges.
 

Users who are viewing this thread

Top Bottom