Using Case statements

  • Thread starter Thread starter Charess
  • Start date Start date
C

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.
 
Will there ever be any unknown's? If not something like below will work in a
query.

USPR: Left([UserPri_SecReasons], 3)
 
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.
 
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.

Jerry Whittle said:
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.

Charess said:
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.
Click to expand...
Click to expand...
 
If it helps, my names will always need to split before the dash, they all
have dashed in them.

Jerry Whittle said:
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.

Charess said:
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.
Click to expand...
Click to expand...
 
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!)


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

Jerry Whittle said:
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.

Charess said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Damn fingers: they'll only do what I tell them to do, not what I want them
to! <g>

Thanks, John.
 
Back
Top