Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Public Function GetBizUnit

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
pdvsa
Asked:
pdvsa
4 Solutions
 
omgangCommented:
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
 
BitsqueezerCommented:
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
 
marlind605Commented:
If that is not working try select case instead

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

Else
        GetBizUnit = "?"
End select
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
pdvsaAuthor Commented:
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
 
PatHartmanCommented:
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
 
pdvsaAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now