Using Case statements

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.

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

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Charess" wrote in message
...
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.

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

"Charess" wrote:

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)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Charess" wrote:

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.

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Charess" wrote in message
...
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.

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" wrote:

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" wrote:

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.

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

"Jerry Whittle" wrote:

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" wrote:

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.

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
http://I.Am/DougSteele
(no e-mails, please!)


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

"Jerry Whittle" wrote:

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" wrote:

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.

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.

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

Thanks, John.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"John Spencer" wrote in message
...
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.