Solved

Public Function GetBizUnit

Posted on 2013-12-11
6
321 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
6 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
If that is not working try select case instead

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

Else
        GetBizUnit = "?"
End select
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pdvsa
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 125 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now