Case When help

[jband]

I am trying to write a query that is dealing with alphanumeric and I am stuck. I have a table that has a client#, Date of service, CPT_Code and amount. The CPT_Code can contain numeric and alphanumeric. What I trying to do is define the code using a case when statement.

Example
Case when CPT_Code between '1' and '9' then 'Payment'
when CPT_Code between '20' and '39' then 'Debit'

The issue I have is the CPT_Code that begins with 2 and has an Alpha at the end needs to be 'Debit'

Can't use like '2%' cause 200 will be defined as 'Charged'

In short is there anyway to query and look for anything that begins with 2 and has an alpha and the end.

Thanks in advance for the help.

If this is confusing, I am sorry. I haven't had enough caffeine yet.

 

[jdraw]

Get some more coffee, then tell us where all this is happening --Access, SQL Server, vba??
Can you put some context on your issue? Sounds like a lot of intelligence built into some custom naming?????

As for

in short is there anyway to query and look for anything that begins with 2 and has an alpha and the end.

Yes, in a query --replace x with your field name

IIf(x Like "2*[a-z]", "Found", "Notfound")

 

[jband]

SQL

I need set the definition to Codes so I can then show the amount to each cataogory (Debit, Charge, etc..)

 

[jband]

Awesome it worked. It was amazing to see it work. Thank you.