INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access 2010 "Select case" Error Handling

Access 2010 "Select case" Error Handling

(OP)
Good morning, I have the following little Function that gives #Error when there's nothing in the particular field that I'm interrogating.

CODE

Public Function fIntent(strInput As String) As String

Select Case strInput

Case "C"
fIntent = "Curative"

Case "D"
fIntent = "Diagnostic"

Case "9"
fIntent = "Not known"

Case "P"
fIntent = "Palliative"

Case "S"
fIntent = "Staging"

Case Else
fIntent = ""

End Select
End Function 

I've tried adding

CODE

Case ""
fIntent = "Not Completed" 

But still get #Error

As far as I know, this is a single character field that is populated from a drop-down list of single charaters.

Any suggestion please?

Many thanks,
D€$

RE: Access 2010 "Select case" Error Handling

(OP)
Although if I use the Immediate Window I can get:

Quote:


?fIntent("")
Not Completed

Many thanks,
D€$

RE: Access 2010 "Select case" Error Handling

I expect the field is Null which is not a string which will cause the error. You can either change the code or send in a string.

CODE --> vba

Public Function fIntent(strInput As Variant) As String
	Select Case strInput
		Case "C"
			fIntent = "Curative"
		Case "D"
			fIntent = "Diagnostic"
		Case "9"
			fIntent = "Not known"
		Case "P"
			fIntent = "Palliative"
		Case "S"
			fIntent = "Staging"
		Case Else
			fIntent = ""
	End Select
End Function 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 "Select case" Error Handling

If PWD wants to distinguish between NULL and "", wouldn't that be:

CODE

Public Function fIntent(varInput As Variant) As String
	Select Case varInput
		Case "C"
			fIntent = "Curative"
		Case "D"
			fIntent = "Diagnostic"
		Case "9"
			fIntent = "Not known"
		Case "P"
			fIntent = "Palliative"
		Case "S"
			fIntent = "Staging"
                Case Is NULL
                        fIntent = "Not Completed"
		Case Else
			fIntent = ""
	End Select
End Function 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Access 2010 "Select case" Error Handling

(OP)
OH yeah, "Is NULL"

Tried Case Null just now, but still get the error.

Just tried the Variant option and get nothing, unfortunately.

I'll be back on Monday and have another look.

Many thanks,
D€$

RE: Access 2010 "Select case" Error Handling

When you get back, please provide the code you are using as well as any error message. You can set a breakpoint and step through your code one line at a time with the [F8] key.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 "Select case" Error Handling

(OP)
Right, not so rushed now :) This does the trick, thanks Andy:-

Quote:


Public Function fIntent(varInput As Variant) As String

Select Case varInput

Case "C"
fIntent = "Curative"

Case "D"
fIntent = "Diagnostic"

Case "9"
fIntent = "Not known"

Case "P"
fIntent = "Palliative"

Case "S"
fIntent = "Staging"

'Case Null
'fIntent = "Not Completed"

Case Else
fIntent = ""

End Select
End Function

Looks like it didn't need either The "Case Null" or "Case Else" to just give me blank cells - although I always like "Case Else"!

Many thanks,
D€$

RE: Access 2010 "Select case" Error Handling

Not

Case Null

but

Case Is Null

RE: Access 2010 "Select case" Error Handling

Quote (PWD)

Looks like it didn't need either The "Case Null" or "Case Else" to just give me blank cells

It's just a guess, but your varInput could be "X", "A", or "7" - which is NOT evaluated by your Select Case - and that's why you've got the default value of fIntent, which is ""

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Access 2010 "Select case" Error Handling

For short lists you can replace Select Case by Switch function:

CODE -->

varOutput=Switch("C", "Curative", "D", "Diagnostic", "9", "Not known", "P", "Palliative", "S", "Staging")
If IsNull(varOutput) Then varOutput = "not completed" 

combo

RE: Access 2010 "Select case" Error Handling

I ignored my opportunity to preach about your data and where it belongs. I'm not aware of your application however I would have a table with the input and output values. Then when another pair is added or something is changed, you modify data rather than modifying code. Data belongs in your tables, not your code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 "Select case" Error Handling

(OP)
strongm, I'd put "Case Null" as "Case Is Null" gives me a Compile error

Quote:

"Expected: = or <> or >< or >= or => or <= or =<"

Andy, at the moment these are the only options - that come from a drop-down list in our system. Or it has just been left un-completed - hence me wanting "Not Completed"

Combo, it's probably me being thick, but

Quote:


Public Function fIntent(varInput As Variant) As String

varOutput = Switch("C", "Curative", "D", "Diagnostic", "9", "Not known", "P", "Palliative", "S", "Staging")
If IsNull(varOutput) Then varOutput = "Not completed"

End Function

= Run-time error '13':

Type mismatch

Having just typed "these are the only options" I decided to just put in:

Quote:


Case Else
fIntent = "Not Completed"

and that appears to do what I'd intended.

And "No", Duane, I have absolutely NO control over the data or its format as this is from a proprietary Cancer Services database! However I'm intrigued by this idea of having a table of codes and their matching meanings. I thought I was being pretty fly managing to use these functions to look them up - can you please explain what you mean and how I'd achieve it?

Many thanks,
D€$

RE: Access 2010 "Select case" Error Handling

Let's assume a small lookup table

tblCodeLookup
Code   CodeDescription
====   =======================
 C     Curative
 D     Diagnostic
 9     Not Known
 P     Palliative
 S     Staging 

You could simply use:
Dlookup("CodeDescription","tblCodeLookup","Code='" & [Your Code Field Here] & "'")

To add or change any codes simply do it in the table. You should never have to code to your code to maintain data.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 "Select case" Error Handling

Sorry:

Case IsNull(varInput)

RE: Access 2010 "Select case" Error Handling

Hopefully you can do what Duane states because that is the correct approach. You could have done a simple workaround as well, although the correct answer has been provided.

Public Function fIntent(varInput As Variant) As String
VarInput = nz(varInput,"NC")

Select Case varInput
Case "C"
...
Case "NC"
fIntent = "Not Completed"
End Select
End Function

RE: Access 2010 "Select case" Error Handling

Sorry, I skipped input in Switch, should be:

CODE -->

Public Function fIntent(varInput As Variant) As String
varOutput = Switch(varInput, "C", "Curative", "D", "Diagnostic", "9", "Not known", "P", "Palliative", "S", "Staging")
If IsNull(varOutput) Then varOutput = "Not completed"
fIntent=cstr(varOutput)
End Function 

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close