pdvsa
asked on
switch
Experts, I have the switch statement below. It doesnt seem to pick up on the bolded part "Offshore". The [BusinessUnit] does say Offshore but the switch does not return
"OFF"...only a blank and not a "?". The Switch does work correctly for all others though. Offshore is the only issue.
Do you see something wrong with it? notice the bold. It is not a spelling mistake. I dont know if there is a limit the criteris switch can handle (I have 8). thank you
=Switch([BusinessUnit]="On shore Houston","OH",[BusinessUni t]="Onshor e Clarement","OC",[BusinessU nit] Like "*PT*","TSW",[BusinessUnit ] Like "*Mexico*","MX",[BusinessU nit] Like "*Genesis*","GEN",[Busines sUnit]="TO F","TOF",[ BusinessUn it]="Subse a","SUB",[ BusinessUnit]="Offshore"," OFF",1=1,"?")
"OFF"...only a blank and not a "?". The Switch does work correctly for all others though. Offshore is the only issue.
Do you see something wrong with it? notice the bold. It is not a spelling mistake. I dont know if there is a limit the criteris switch can handle (I have 8). thank you
=Switch([BusinessUnit]="On
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff: thanks for the response.
1. 1=1,"?"
...If you remove it, does this all work?
answer===>I tried and it did not work still
2. Does it work for Offshore if you just create a switch function just of Offshore:
=switch([BusinessUnit]="Of fshore","O FF")
===>It did not
3. Try swapping it
===>that didnt work either
I made the Function hoping that would work but it didnt either :(. It returned the same results as the switch inside the control source as I initially had it.
here is my Function:
maybe I have something wrong with it. If you could let me know would appreciate.
How could I modify the function to return a "?" if it is not in the list? I had this in the switch statement. Maybe if it returned a ? then might have some direction.
thank you
1. 1=1,"?"
...If you remove it, does this all work?
answer===>I tried and it did not work still
2. Does it work for Offshore if you just create a switch function just of Offshore:
=switch([BusinessUnit]="Of
===>It did not
3. Try swapping it
===>that didnt work either
I made the Function hoping that would work but it didnt either :(. It returned the same results as the switch inside the control source as I initially had it.
here is my Function:
maybe I have something wrong with it. If you could let me know would appreciate.
Public Function GetBizUnit(BizUnit As String) As String
If BizUnit = "Onshore Houston" Then
GetBizUnit = "OH"
ElseIf BizUnit = "Onshore Clarement" Then
GetBizUnit = "OC"
ElseIf BizUnit Like "*Process Tech*" Then
GetBizUnit = "PT"
ElseIf BizUnit = "Mexico*" Then
GetBizUnit = "MX"
ElseIf BizUnit Like "*Genesis*" Then
GetBizUnit = "GEN"
ElseIf BizUnit = "TOF" Then
GetBizUnit = "TOF"
ElseIf BizUnit = "Subsea" Then
GetBizUnit = "SUB"
ElseIf BizUnit = "Offshore and Subsea" Then
GetBizUnit = "OH"
ElseIf BizUnit = "Offshore" Then
GetBizUnit = "OFF"
End If
How could I modify the function to return a "?" if it is not in the list? I had this in the switch statement. Maybe if it returned a ? then might have some direction.
thank you
@Jeff,
I generally put the last expression of the SWITCH as something that returns true (I actually use a -1 and "Other"), but 1=1 is true, so if none of the other criteria are met, the final expression should result in the SWITCH( ) function returning the "?"
@Tony,
Based on your responses to the other questions (mine and Jeff's), I would guess that field containing the word "Offshore" has a space in it somewhere.
To modify the function, insert an ELSE statement just before the end if
ELSE
GetBizUnit = "?"
End IF
If you call your function and pass it the "Offshore" value from the immediate window, what does it return?
?GetBizUnit("Offshore")
I generally put the last expression of the SWITCH as something that returns true (I actually use a -1 and "Other"), but 1=1 is true, so if none of the other criteria are met, the final expression should result in the SWITCH( ) function returning the "?"
@Tony,
Based on your responses to the other questions (mine and Jeff's), I would guess that field containing the word "Offshore" has a space in it somewhere.
To modify the function, insert an ELSE statement just before the end if
ELSE
GetBizUnit = "?"
End IF
If you call your function and pass it the "Offshore" value from the immediate window, what does it return?
?GetBizUnit("Offshore")
ASKER
Fyed:
I thought there was certainly a space too but I checked and there is not.
I attached the table in excel format. It is an export directly from the db.
Maybe either of you guys can see a mistake somewhere.
fyed:
It did return OFF in the immediate window.
here are the results:
?getBizUnit("Offshore")
OFF
tblBusinessUnit.xlsx
I thought there was certainly a space too but I checked and there is not.
I attached the table in excel format. It is an export directly from the db.
Maybe either of you guys can see a mistake somewhere.
fyed:
It did return OFF in the immediate window.
here are the results:
?getBizUnit("Offshore")
OFF
tblBusinessUnit.xlsx
ASKER
I see that it does work when I remove the switch from the control source and put BU: GetBizUnit([tblBusinessUni t].[Busine ssUnit]) in the query
?
?
ASKER
it is because the control source was a combo box with a row source query on it. I changed it to a text box and it works.
I shall split points on this one. Any objects let me know.
I shall split points on this one. Any objects let me know.
Does the case and spelling of "Offshore" match the case and spelling in the [BusinessUnit] field?
ASKER
aikimark: yes. Both are spelled "Offshore" with a capital "O" and there is not a space. I think the issue was with the field as being a combo box and not a text box.
Tony,
"I think the issue was with the field as being a combo box and not a text box. "
Don't confuse "fields" with "controls".
"I think the issue was with the field as being a combo box and not a text box. "
Don't confuse "fields" with "controls".
ASKER
oopps....I have to stop that.
Don't forget trailing blanks
ASKER