Full Version: The String Returned By The Builder Was Too Long
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
ThankYou1
Couple of questions actually.
)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.
Larry Larsen
Hi
Are these values actually text or numeric....!!!!
" style="vertical-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
ThankYou1
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).
ThankYou1
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).
Is 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.
Larry Larsen
Hi
Ok.. can you show us some real codes and how you split them up...
" style="vertical-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
ThankYou1
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
FROM Cardiology1
GROUP BY Left([CPT],5), Mid([CPT],9);
The codes in my earlier posts are real codes in the data.
ThankYou1
Here's a sample:
048T
0050T
0126T
99291
99292
99344
99345
99354
99355
99356
99357
99396
99499
A9579
G0008
G0127
G0275
G0278
G9141
G9142
J1940
Q9967
Thanks
datAdrenaline
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")
ThankYou1
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.