Solved

switch

Posted on 2013-06-30
14
457 Views
Last Modified: 2013-06-30
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,"?")
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 200 total points
ID: 39288536
It works for me, as written.  Are you sure that you have a record which should match that criteria that doesn't also match one of the previous criteria?

What version of Access are you using?

Is this in a control source, or as a computed field in a query?
0
 

Author Comment

by:pdvsa
ID: 39288639
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.
0
 

Author Comment

by:pdvsa
ID: 39288646
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

 Offshore_is_blank
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 39288704
1. I don't work with the switch function all that often, ...but what is that last expression doing: :
    1=1,"?"
...If you remove it, does this all work?

2. Does it work for Offshore if you just create  a switch function just of Offshore:
=switch([BusinessUnit]="Offshore","OFF")

3. I have seen some links that say that there is a 7 expression limit for the switch function... ...but fyed states it works ok for him...
...and just by looking at it, I see nothing wrong either...

4. Try swapping the first and last expressions and see what happens:

=Switch([BusinessUnit]="Offshore","OFF",[BusinessUnit]="Onshore Clarement","OC",[BusinessUnit] Like "*PT*","TSW",[BusinessUnit] Like "*Mexico*","MX",[BusinessUnit] Like "*Genesis*","GEN",[BusinessUnit]="TOF","TOF",[BusinessUnit]="Subsea","SUB",[BusinessUnit]="Onshore Houston","OH",1=1,"?")

5. Consider using a more normalized approach where the Switch function would not be needed.

6. Try going "old-School" and using an If/Then/Else, ...in a public function, in code.
Something roughly like this:

'--------------------------------------------------------------------------------
Public Function GetBizUnit(BizUnit as string) as string
If BizUnit="Onshore Houston"Then
    GetBizUnit="OH"
ElseIf BizUnit="Onshore Clarement" Then
    GetBizUnit="OC"
...
...
ElseIf BizUnit="Offshore" Then
    GetBizUnit="OFF"
End if
'--------------------------------------------------------------------------------

...Then call it like this: =GetBizUnit([BusinessUnit])

Hope some of this helps as well

;-)

JeffCoachman
0
 

Author Comment

by:pdvsa
ID: 39288728
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39288742
@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")
0
 

Author Comment

by:pdvsa
ID: 39288761
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
0
 

Author Comment

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

?
0
 

Author Comment

by:pdvsa
ID: 39288839
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39288860
Does the case and spelling of "Offshore" match the case and spelling in the [BusinessUnit] field?
0
 

Author Comment

by:pdvsa
ID: 39288864
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39288866
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".
0
 

Author Comment

by:pdvsa
ID: 39288870
oopps....I have to stop that.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39288915
Don't forget trailing blanks
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question