Writing a query that uses the number of characters in the criteria

[Jessica240]

Hello everyone. This should be a pretty easy question.

I have a table with a field that has text as the data type. This field is called "CPT" and has many different CPT codes (some codes contain letters and numbers, others contain just numbers).

What I need to do is be able to look at the data in the table where the CPT field only has exactly 6 digits.

So for example: say the data in the CPT field looks like this:

CPT


12345
123456
1234567

Then in the results of my query, all I want to show up is the 123456. Not the other two codes.

What do I use in the criteria line? I prefer to use the design view and not SQL.

Thanks for your help!

Jessica

[June7]

Then you have to create a field with an expression:

LengthCPT: Len([CPT])

Then criteria under that constructed field: 6

Can uncheck the Show box if you don't want that field to display.

[Jessica240]

WOW! Geeze that was too easy!!!! But thank you, that was extremely helpful!!!! I really appreciate your help!

Jessica

[Jessica240]

Ok now I have another question....

What I am trying to do is tell Access that if the length of the string in the CPT field is 7 and the CPT code ends in "50", then multiply the rate in one field by 1.5. Here is my expression:

2014 RATE: IIf([CPT]="*50" And [lengthcpt]=7,[therate]*1.5,[rate])

But what happens when I run the query is I get a null value when the CPT code ends in 50.

When the CPT code does not end in 50, I get the rate - which is good because that's what I want. But for some reason Access will not give me the rate*1.5 when I need it to.

I'm guessing its because the part of the expression CPT = "*50" is wrong..... not sure how to fix it :/

[ssanfu]

Try

2014 RATE: IIf(Right([CPT],2) = "50" And [lengthcpt]=7,[therate]*1.5,[rate])

Why do yo have [therate]*1.5 instead of [rate]*1.5???

[Jessica240]

Its kind of complicated.... basically I have a Payment Rates table with about 15,000 CPT codes with a payment rate for each CPT code. Each CPT code in this table is a 5-digit code.

Sometimes, when I import a new table of codes, a CPT code will have a "50" at the end of it (making it 7 digits total), signifying that the doctor performed a more complicated version of the 5-digit procedure.

My problem is that my payment rates table only has the 5-digit codes in it (not the ones with the 50 at the end). The payment rate for the code with the 50 at the end of it needs to be 1.5 times that of the 5-digit code.

So in the Payment Rate table: if a 12345 has a payment rate of $5.00

then the payment rate for a 1234550 needs to be $7.50 (5 * 1.5)

[rate] = the payment rate directly from the payment rate table

[CODE] = left([cpt], 5) < this allows Access to take the "50" off the end and give me just the 5-digit code

[therate] = [rate] where [CODE] = [CPT]

So what I did there was I created a query that looks at the 7-digit code and creates a field (called CODE) with just the first 5 digits of that code. Then, in a subsequent query, I link the [CODE] to the [CPT] field in the Payment Rates Table. The result is that I get the rate for the 5-digit code.

By creating [therate], I have allowed Access to retrieve the rate I need from the payment rates table - then now I need that multiplied by 1.5 and then I will have the rate I need for the 7-digit code.

Does that make sense? Its hard to explain all that...

[Jessica240]

Steve - your expression worked PERFECTLY!!!! Thank you so much for your help! I truly appreciate it!!!

Jessica

[ssanfu]

You're welcome. Glad to be of help