Case Select

[RPW1]

I Have a field called "CollID in a table "test" where I want to assign a number value based on a between statement in a 2 character text field called "prefix" there are 4 variables. I'm a novice at opening a table and using a case statement and edit the field. Any help you can provide is appreciated.
RPW1

 

[Sawatzky]

dear rpw,

please tell us more exactly what you want to do.

regards astrid

 

[RPW1]

The ID# for the Field Collid is based on the 2 character field Prefix. if Prefix is between AA an FF for examples the value in collid should be 1. or if Between FG and LL it is 2 and so on for 4 Id#s. I was able to do it in series of queries or iif statements but I heard the case select method in code is easier. The table is called test for now.
Bob

 

[Sawatzky]

Now I got you,

I'am sorry but I have not the exact coding in mind, and my books are at work and I am at home.
Hopefully somoneelse can help you. Otherwise I give you the answer tomorrow

regards astrid

 

[Sawatzky]

just found this in another thread

The SQL for Sql server 2000 has a case statement. Here is an example from the Sql server
help.
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price

so for you this should be something like

SELECT 'collid' =
CASE
WHEN collid between 'AA' and 'FF' THEN 1
WHEN collid between 'FG' and 'LL' then 2
...

END,

FROM testtable

regards astrid

 

[PaulF]

Try something like this Function


Function CollIdValue(strPrefix As String)
Select Case Asc(UCase(Left(strPrefix, 1)))
Case Is < 70 'A,B,C,D,E
CollIdValue = &quot;1&quot;
Case Is = 70 'F
Select Case Asc(UCase(Right(strPrefix, 1)))
Case Is < 71 'G
CollIdValue = &quot;1&quot;
Case Else
CollIdValue = &quot;2&quot;
End Select
Case 71 To 75 'G,H,I,J,K
CollIdValue = &quot;2&quot;
Case 76 'L
Select Case Asc(UCase(Right(strPrefix, 1)))
Case Is < 77 'M
CollIdValue = &quot;2&quot;
Case Else
CollIdValue = &quot;3&quot;
End Select
Case 77 To 81 'M,N,O,P,Q
CollIdValue = &quot;3&quot;
Case 82 'R
Select Case Asc(UCase(Right(strPrefix, 1)))
Case Is < 83 'S
CollIdValue = &quot;3&quot;
Case Else
CollIdValue = &quot;4&quot;
End Select
Case Else
CollIdValue = &quot;4&quot;
End Select

End Function

Then just pass the Prefix field to the Function

CollID = CollIdValue([Prefix])


PaulF

 

[Sawatzky]

dear RPW,


this statemant was tested against a sql2000 server

select myresult = case
when collid between 'AA' and 'FF' then 10
when collid between 'FG' and 'LL' then 11
else 20
end


from projekt

you need to introduce an alias as you change datatype


regards astrid