Using Case statements

[Charess]

I have a case statement in a module which I then call upon in the expression
builder in a query. I hope that this is the correct way to do this.

I had originally had an IF statement in my query that look at the field to
the left of it, but I ran out of characters and received a truncate error.

For some reason I get a datatype mismatch when I open my query.

in query design the expression is: Expr1: callConvert()

Here is the code behind it: Sub Convert()

Select Case UserPri_SecReasons
Case "BJB - BP"
UserPri_SecReasons = "BJB"
Case "BJB - CC"
UserPri_SecReasons = "BJB"
Case "BJO - L"
UserPri_SecReasons = "BJO"
Case "C/B - B"
UserPri_SecReasons = "C/B"
Case Else
UserPri_SecReasons = "Unknown"
End Select
End Sub
___________________________________________________________________
Function callConvert()
Call Convert
End Function


UserPri_SecReasons is the name of the field I need to convert in my query.

All help is appreciated, I've been working at this for hours.

 

[Arvin Meyer [MVP]]

Have you used Lookup fields in your table? If so, you need to get rid of
them:

http://www.mvps.org/access/lookupfields.htm

 

[Charess]

I do have lookup fields but not in the table that the query is based on...

 

[Jerry Whittle]

Will there ever be any unknown's? If not something like below will work in a
query.

USPR: Left([UserPri_SecReasons], 3)

 

[Douglas J. Steele]

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.

 

[Charess]

I don't have unknowns, but my list of results will not always be 3
characters. I shorten the list for this example. Is my naming convention
correct. Should I use Function & End Function instead of Sub & End Sub. I'm
think it has something to do with the naming maybe? I am very new to Access
and have no clue what sub, dim, or any other naming conventions mean.

Thanks.

Will there ever be any unknown's? If not something like below will work in a
query.

USPR: Left([UserPri_SecReasons], 3)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a case statement in a module which I then call upon in the expression
builder in a query. I hope that this is the correct way to do this.

I had originally had an IF statement in my query that look at the field to
the left of it, but I ran out of characters and received a truncate error.

For some reason I get a datatype mismatch when I open my query.

in query design the expression is: Expr1: callConvert()

Here is the code behind it: Sub Convert()

Select Case UserPri_SecReasons
Case "BJB - BP"
UserPri_SecReasons = "BJB"
Case "BJB - CC"
UserPri_SecReasons = "BJB"
Case "BJO - L"
UserPri_SecReasons = "BJO"
Case "C/B - B"
UserPri_SecReasons = "C/B"
Case Else
UserPri_SecReasons = "Unknown"
End Select
End Sub
___________________________________________________________________
Function callConvert()
Call Convert
End Function


UserPri_SecReasons is the name of the field I need to convert in my query.

All help is appreciated, I've been working at this for hours.

 

[Charess]

If it helps, my names will always need to split before the dash, they all
have dashed in them.

Will there ever be any unknown's? If not something like below will work in a
query.

USPR: Left([UserPri_SecReasons], 3)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a case statement in a module which I then call upon in the expression
builder in a query. I hope that this is the correct way to do this.

I had originally had an IF statement in my query that look at the field to
the left of it, but I ran out of characters and received a truncate error.

For some reason I get a datatype mismatch when I open my query.

in query design the expression is: Expr1: callConvert()

Here is the code behind it: Sub Convert()

Select Case UserPri_SecReasons
Case "BJB - BP"
UserPri_SecReasons = "BJB"
Case "BJB - CC"
UserPri_SecReasons = "BJB"
Case "BJO - L"
UserPri_SecReasons = "BJO"
Case "C/B - B"
UserPri_SecReasons = "C/B"
Case Else
UserPri_SecReasons = "Unknown"
End Select
End Sub
___________________________________________________________________
Function callConvert()
Call Convert
End Function


UserPri_SecReasons is the name of the field I need to convert in my query.

All help is appreciated, I've been working at this for hours.

 

[Douglas J. Steele]

In that case, you could use

USPR: Left([UserPri_SecReasons], InStr([UserPri_SecReasons], " - ") - 2)

If there's a chance that there may not be exactly one space on either side
of the dash, use

USPR: Trim(Left([UserPri_SecReasons], InStr([UserPri_SecReasons], "-") - 1))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If it helps, my names will always need to split before the dash, they all
have dashed in them.

Will there ever be any unknown's? If not something like below will work
in a
query.

USPR: Left([UserPri_SecReasons], 3)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a case statement in a module which I then call upon in the
expression
builder in a query. I hope that this is the correct way to do this.

I had originally had an IF statement in my query that look at the field
to
the left of it, but I ran out of characters and received a truncate
error.

For some reason I get a datatype mismatch when I open my query.

in query design the expression is: Expr1: callConvert()

Here is the code behind it: Sub Convert()

Select Case UserPri_SecReasons
Case "BJB - BP"
UserPri_SecReasons = "BJB"
Case "BJB - CC"
UserPri_SecReasons = "BJB"
Case "BJO - L"
UserPri_SecReasons = "BJO"
Case "C/B - B"
UserPri_SecReasons = "C/B"
Case Else
UserPri_SecReasons = "Unknown"
End Select
End Sub
___________________________________________________________________
Function callConvert()
Call Convert
End Function


UserPri_SecReasons is the name of the field I need to convert in my
query.

All help is appreciated, I've been working at this for hours.

 

[John Spencer]

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]

Damn fingers: they'll only do what I tell them to do, not what I want them
to! <g>

Thanks, John.

 

[Charess]

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