Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

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]="Onshore Houston","OH",[BusinessUnit]="Onshore Clarement","OC",[BusinessUnit] Like "*PT*","TSW",[BusinessUnit] Like "*Mexico*","MX",[BusinessUnit] Like "*Genesis*","GEN",[BusinessUnit]="TOF","TOF",[BusinessUnit]="Subsea","SUB",[BusinessUnit]="Offshore","OFF",1=1,"?")
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

ASKER

Hey fyed.... Yes j am sure there is a record.  Its a computed field in a query.  I am using 2007.  Let me get my computer out & make sure it is a computed field in query.
Avatar of pdvsa

ASKER

fyed:  it is actually in the control source.

I have attached the data returned from the query (on the left) and the report (the right).

Any idea why it returns blank in the report for only Offshore?  

thank you

 User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

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]="Offshore","OFF")
===>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
    

Open in new window



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")
Avatar of pdvsa

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
Avatar of pdvsa

ASKER

I see that it does work when I remove the switch from the control source and put BU: GetBizUnit([tblBusinessUnit].[BusinessUnit])  in the query

?
Avatar of pdvsa

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.
Does the case and spelling of "Offshore" match the case and spelling in the [BusinessUnit] field?
Avatar of pdvsa

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".
Avatar of pdvsa

ASKER

oopps....I have to stop that.
Don't forget trailing blanks