Solved

Public Function GetBizUnit

Posted on 2013-12-11
6
323 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

13 Experts available now in Live!

Get 1:1 Help Now