Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access 2010 "Select case" Error Handling

Status
Not open for further replies.
PWD

PWD

Technical User
Jul 12, 2002
823
GB
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€$
 
Sort by date Sort by votes
Although if I use the Immediate Window I can get:

?fIntent("")
Not Completed
Click to expand...

Many thanks,
D€$
 
Upvote 0 Downvote
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: 
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
 
Upvote 0 Downvote
If PWD wants to distinguish between NULL and "", wouldn't that be:

Code: 
Public Function fIntent([blue]var[/blue]Input As Variant) As String
	Select Case [blue]var[/blue]Input
		Case "C"
			fIntent = "Curative"
		Case "D"
			fIntent = "Diagnostic"
		Case "9"
			fIntent = "Not known"
		Case "P"
			fIntent = "Palliative"
		Case "S"
			fIntent = "Staging"[blue]
                Case Is NULL
                        fIntent = "Not Completed"
[/blue]		Case Else
			fIntent = ""
	End Select
End Function

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
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€$
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Right, not so rushed now :) This does the trick, thanks Andy:-

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
Click to expand...

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€$
 
Upvote 0 Downvote
PWD said:
Looks like it didn't need either The "Case Null" or "Case Else" to just give me blank cells
Click to expand...

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.
 
Upvote 0 Downvote
For short lists you can replace [tt]Select Case[/tt] by [tt]Switch[/tt] function:
Code: 
varOutput=Switch("C", "Curative", "D", "Diagnostic", "9", "Not known", "P", "Palliative", "S", "Staging")
If IsNull(varOutput) Then varOutput = "not completed"

combo
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
strongm, I'd put "Case Null" as "Case Is Null" gives me a Compile error

"Expected: = or <> or >< or >= or => or <= or =<"
Click to expand...

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
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
Click to expand...

= Run-time error '13':

Type mismatch

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

Case Else
fIntent = "Not Completed"
Click to expand...

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€$
 
Upvote 0 Downvote
Let's assume a small lookup table

[pre]tblCodeLookup
Code CodeDescription
==== =======================
C Curative
D Diagnostic
9 Not Known
P Palliative
S Staging[/pre]

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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Sorry, I skipped input in [tt]Switch[/tt], 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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
648
lameid
lameid
Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
343
Swi
Swi
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
635
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Select addresses using two criteria, one 'Like&quot; 2
Replies
3
Views
171
TrekBiker
TrekBiker
monkeysee
  • Locked
  • Question
Select Query for append query 1
Replies
12
Views
269
monkeysee
monkeysee

Part and Inventory Search

Sponsor

Back
Top