Dec 3 2010, 10:05 AM
Couple of questions actually.
1)Anybody know the text for a criteria statement that includes both the Not and Between functions? So for example if I wanted to get a list off all items that are not between 99201 and 99205, 99241 and 99245, 99251 and 99255, etc. I tried <>Between "99201" and "99205", <> Between "99241" and "99245", etc.
2) Because I didn't get the Not Between to work, I broke up all the codes I needed to exclude from my list and came up with a query that resembles this:
<>"99201" And <>"99202" And <>"99203" And <>"99204" And <>"99205" And <>"99211" And <>"99212" And <>"99213" And <>"99214" And <>"99215" And ...
but that gave me over 100 codes - which I unhappily and painstakingly entered, only to receive the "The string returned by the builder was too long" error message.
So needless to say that you can save the life of my computer if you help. Otherwise, the people on the street should be careful of the flying computer.
Thank you for your help.
Dec 3 2010, 10:12 AM
Are these values actually text or numeric....!!!!
Dec 3 2010, 10:38 AM
They are indeed text. In the table, the codes are stored in one column that combines them with a description. I used a query to make 2 spearate fields (code and description).
Dec 3 2010, 11:24 AM
I'm thinking if you used the Val() function it will change the split value into numeric..
Then may be you could use:
>= 99201 and <= 99205
>= 99241 and <= 99245
>= 99251 and <= 99255
As criteria in the query....
Dec 3 2010, 11:39 AM
Just tried, the issue however is that the Val() function removed the leading 0's and any letters that are part of the code. Sorry - I neglected to mention that there are some codes that have a letter (0048T became 48, A9579 became 0).
As an aside though, at least I learned about the Val() function!
It seems I can't really make the codes into numeric because of the letters some of them contain. I do still have the list of codes listed individually but again, the list is over 100 codes and I will jet get that error message if i try to use it.
Dec 3 2010, 11:44 AM
Ok.. can you show us some real codes and how you split them up...
Dec 3 2010, 01:01 PM
Not positive about what you are asking but here is the SQL:
SELECT Left([CPT],5) AS [CPT Code], Mid([CPT],9) AS Description, Sum(Cardiology1.[Frequency / Units]) AS [SumOfFrequency/Units], Sum(Cardiology1.Billings) AS SumOfBillings
GROUP BY Left([CPT],5), Mid([CPT],9);
The codes in my earlier posts are real codes in the data.
Dec 3 2010, 01:11 PM
Sorry I most likely didn't explain..
What I was asking for is a small general list of varying codes that your dealing with..
Looking to see the general format these values of data..
Dec 3 2010, 02:32 PM
Here's a sample:
Dec 3 2010, 03:25 PM
You should use a NOT BETWEEN, not <> BETWEEN. Understand that (someField BETWEEN x AND y) is sort of like a function that returns True or False, so (from an SQL View standpoint) your SQL statement would look something like this:
SELECT * FROM someTable
WHERE NOT (someField BETWEEN "99201" AND "99205") And NOT (someField BETWEEN "99241" AND "99245") And ...
So, in the criteria row of the Query Designer, you'd add something like this as criteria for the field in question:
Not (Between "99201" and "99205") And Not (Between "99241" and "99245")
Dec 3 2010, 03:44 PM
If I were a game show host, you'd have won a car. Many thanks.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here