If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Using Case statements
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. |
#3
|
|||
|
|||
Using Case statements
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. |
#4
|
|||
|
|||
Using Case statements
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. |
#5
|
|||
|
|||
Using Case statements
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. |
#6
|
|||
|
|||
Using Case statements
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. |
#7
|
|||
|
|||
Using Case statements
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. |
#8
|
|||
|
|||
Using Case statements
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. |
#9
|
|||
|
|||
Using Case statements
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. |
#10
|
|||
|
|||
Using Case statements
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. |
|
Thread Tools | |
Display Modes | |
|
|