Solved

Public Function GetBizUnit

Posted on 2013-12-11
6
328 Views
Last Modified: 2013-12-29
Experts,

I have this function below.
It abbreviates business units.
I am not sure if there is a conflict.
If Business Unit = "Onshore Claremont" then it returns a "?" and not OC like it should.  
I have remmed out the unlderlined lines completely and separately but still wasnt the solution.

let me know if you see something or have any other suggestion.

Public Function GetBizUnit(BizUnit As String) As String

    If BizUnit = "Onshore Houston" Then
        GetBizUnit = "OH"
    ElseIf BizUnit = "Onshore Houston (PT)" Then
        GetBizUnit = "PTH"
   ElseIf BizUnit = "Onshore Claremont" Then
        GetBizUnit = "OC"
    ElseIf BizUnit = "Onshore Claremont (PT)" Then
       GetBizUnit = "PTC"
    ElseIf BizUnit = "Mexico*" Then
        GetBizUnit = "MX"
    ElseIf BizUnit = "Onshore 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"
       
    Else
        GetBizUnit = "?"
    End If
   
       
End Function
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
6 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 125 total points
ID: 39712245
Add this and test again so we can see what's being passed in

Public Function GetBizUnit(BizUnit As String) As String

    Debug.Print BizUnit & " ---- length = " & Len(BizUnit)

    If BizUnit = "Onshore Houston" Then


OM Gang
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 125 total points
ID: 39712323
Hi,

it is most likely that the value you pass to the function is not the value you check.
Try this:
Debug.Print "'" & BizUnit & "'"

On this way you can see for example if there is any space in the value before or after the string.

Moreover you should have the standard "Option Compare Database" as first row in the module, if this is missing, the comparison works with binary compare and in this case upper and lower case text comparison is not the same in the whole module.

Another thing: Instead of using If and ElseIf you should simply use "Select Case" instead, that makes it easier to enter and to read.

But you are in a database environment and so I would say this whole thing is not needed. Simply create a table with two columns where you enter the corresponding values and then you can use a simple "DLookup" function which works in both directions - and so you function reduces to just a single line of code.

Cheers,

Christian
0
 

Assisted Solution

by:marlind605
marlind605 earned 125 total points
ID: 39712389
If that is not working try select case instead

Select Case(Bizunit)
   GetBizUnit = "OH"
' All the other cases

Else
        GetBizUnit = "?"
End select
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:pdvsa
ID: 39714050
thank you...sorry for not responding earlier.  I will not be able to get back until end of day...very busy at "real" job.

thank you.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 125 total points
ID: 39714187
Rather than using code to convert this value, you should be using a table.  The advantage of a table is ease of maintenance.  If you add a new value, you simply add it to the table and don't have to change any code.  In most cases, users can handle their own table maintenance and don't need to involve the programming staff.  So, I would create a lookup table but rather than using DLookup() to retrieve the value I would use a left join.  I choose a left join for lookup tables because there may be a case where the value is missing and you don't want to lose the main record because of that.  

To implement this, you would modify your ReocrdSource query to include the join and pick up the conversion field from the lookup table.  That allows you to bind the lookup value to a form field and 0 lines of code are required.

If you are having trouble matching the lookup values, it is quite likely because one side or the other contains an extra space.  Since the data is in a table, it will be easy to isolate.  Create a totals query that selects only the Bizunit field and groups by it.  You can add a count if you are interested.  The result will be a list of the Bizunit values in the table.  If the "same" value occurs more than once, you will need to find those records and correct the extraneous space issue.

Onshore Houston
Onshore Houston (PT)
Onshore Claremont
Onshore  Claremont
Onshore Claremont (PT)
...
The second instance of Onshore Claremont has an extra space.
0
 

Author Closing Comment

by:pdvsa
ID: 39745470
I am not certain what is wrong.  It is not that impt to me at the moment.   I thought splitting pts is best.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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