Select Case

[ste4en]

I am trying to use a Select Case statement instead of an IIF statement in a query. I think my attempt is self explainatory below but it is causing me problems and I suspect my syntax is wrong. I started off making it a public function but that did not work either:

In a select query I want to be able to use get the cost code for the activity code using something like CostCode: CostCodeN(ActivityCode)

Any help appreciated.
Thanks
Ste4en

Option Compare Database

Select Case CostCodeN
'determines the correct costcode for this activity code

Case Left(ActivityCode, 1) = "0": CostCode = ActivityCode

Case Left(ActivityCode, 1) = "9": CostCode = ActivityCode

Case Left(ActivityCode, 1) = "5": CostCode = "500120"

Else: CostCode = Left(ActivityCode, 3) & "000"

End Select

 

[JerryKlmns]

Function CostCodeN(myActivityCode) As String

Select Case Left(ActivityCode, 1)
  Case Is "0"
    CostCodeN = myActivityCode
  Case Is "9"
    CostCodeN = ActivityCode
  Case Is "5"
    CostCodeN = "500120"
  Case Else
    CostCodeN = Left(ActivityCode, 3) & "000"
End Select
End Function

 

[JerryKlmns]

Forgot the SQL part

SELECT CostCodeN([ActivityCode]) As CostCode
FROM ...
WHERE ...

The function is to be placed in a module as is

 

[oharab]

What you're written seems ok, apart from the important bit: You've not wrapped it in a function!

Function CostCodeN(ActivityCode as String) as String
'determines the correct costcode for this activity code

Select Case ActivityCode

Case Left(ActivityCode, 1) = "0"
 CostCodeN = ActivityCode

Case Left(ActivityCode, 1) = "9"
 CostCodeN = ActivityCode

Case Left(ActivityCode, 1) = "5"
 CostCodeN = "500120"

Case Else
 CostCodeN = Left(ActivityCode, 3) & "000"

End Select

End Function

You can then use the function in your query as you describe.

You could also create a lookup table for the CostCode's and join it in your query. You may get better performance this way, depending how big your dataset is.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[oharab]

Jerry, you have a couple of typos & "Case Is.." is an invalid syntax, but, looking at yours, it may be slightly more efficient as the left() calculation is only done once, so I'd say:

Function CostCodeN(myActivityCode as string) As String

Select Case Left(myActivityCode, 1)
  Case Is "0"
    CostCodeN = myActivityCode
  Case Is "9"
    CostCodeN = myActivityCode
  Case Is "5"
    CostCodeN = "500120"
  Case Else
    CostCodeN = Left(myActivityCode, 3) & "000"
End Select
End Function

You may also want to put some error handling in if a null or empty string is passed through.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[ste4en]

Thanks both for your replies.
The dataset will not be too large maybe 1,500 records in any week. I think the Case option will work fine however the lookup table I think would be better allowing a more visual aid for each project to set thier own conversion table up rather than me having to edit the Case statement. I did first try this method but I could not see how to make the join since it would have wildcards the table would have two columns:

Activity Code | Cost Code
0* | [Cost Code]
9* | [Cost Code]
5* | 500120


Is this possible or would I have to have every Activity Code listed in full with the related Cost Code.

Thanks Again.

 

[oharab]

The quickest way is to have every costcode in the lookup table.
You can do it using wild cards, but you would have to use function in your query rather than SQL joins, so the performance gain would be lost.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[ste4en]

I am trying to get away from the cross reference table due to the number of errors we are having; I am working on the case statement now; its not working yet...
Thanks

 

[ste4en]

Thanks both.... its working. Would not for a while until I found some Null values. I can filter for these in the Query, however would it best to capture them in the function (if so how)?

Function CostCodeN(myActivityCode As String) As String
'determines the correct costcode for this activity code


Select Case Left(myActivityCode, 1)
  Case "0"
    CostCodeN = myActivityCode
  Case "9"
    CostCodeN = myActivityCode
  Case "5"
    CostCodeN = "500120"
  Case Else
    CostCodeN = Left(myActivityCode, 3) & "000"
End Select
End Function

 

[oharab]

You just need to change the input variable to variant to accept nulls and check for it on the way in.

Function CostCodeN(myActivityCode As [b]variant[/b]) As String
'determines the correct costcode for this activity code
On Error Goto 
If IsNull(myActivityCode) then
 CostCodeN=""
 Exit Function
 'Exit the function early, there's no need to run the select case.
End If
Select Case Left(myActivityCode, 1)
  Case "0"
    CostCodeN = myActivityCode
  Case "9"
    CostCodeN = myActivityCode
  Case "5"
    CostCodeN = "500120"
  Case Else
    CostCodeN = Left(myActivityCode, 3) & "000"
End Select
End Function

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[ste4en]

It did not like the "On Error Goto" so I deleted the line and it does axactly what I need.

Thanks again.

 

[oharab]

Sorry, forgot to delete that!

Glad it's all up and running for you.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.