Solved

Public Function GetBizUnit

Posted on 2013-12-11
6
326 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 35

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

792 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