Using Case statements

Thank you! I was able to use the left formula to break out my reason. Your a
life save!

"John Spencer" wrote:

Small typo in Douglas' example.

In order to return a value you need a FUNCTION.

Public FUNCTION Convert(ValueIn) as String

Select Case ValueIn & ""
Case "BJB - BP"
Convert = "BJB"
Case "BJB - CC"
Convert = "BJB"
Case "BJO - L"
Convert = "BJO"
Case "C/B - B"
Convert = "C/B"
Case Else
Convert = "Unknown"
End Select

End Function

As noted elsewhere, you can use Left and Instr to return the desired value

Left(UserPri_SecReasons,Instr(1,UserPri_SecReasons & "-","-")-1)

That will return the entire value of UserPri_SecReasons if there is no "-"
character. If the field is null, you should get a zero-length string returned.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Douglas J. Steele wrote:
Your sub has no idea what UserPri_SecReasons is, since it's not declared,
nor is a value passed to the sub.

The fact that you're not getting an error message implies that you haven't
told Access to require that all variables be declared (see what Allen Browne
has at http://allenbrowne.com/ser-30.html in the section on Option Explicit)

Sounds as though what you need is something like:

Function Sub Convert(ValueIn) As String

If IsNull(ValueIn) = False Then
Select Case ValueIn
Case "BJB - BP"
Convert = "BJB"
Case "BJB - CC"
Convert = "BJB"
Case "BJO - L"
Convert = "BJO"
Case "C/B - B"
Convert = "C/B"
Case Else
Convert = "Unknown"
End Select
Else
Convert = "Unknown"
End If

End Function

You'd then have to call that function in a query, passing it the value of
UserPri_SecReas.