string comparison in query

[mpearce]

I have a diagnosis code field in a database with a datatype of text. some of the entries are just numbers and some start with a letter and all other characters are numbers.

I have categories that have ranges of codes that fit into them. for example one of my categories is all diagnosis codes that fall between 630.xx and 679.xx where xx could be any number. another would be 249.xx to 249.xx or 250.xx to 250.xx, or 295.xx to 300.xx, or E800.xx to E999.xx, there are a lot more than that but i wont burden you with all of them. I would like some help in wiriting queries using these ranges in a where clause.

Is it possible to use <, >, <=, >= with strings in a query? Or is there a better way to do this?

[OBP]

I think the best way would bt use the Like function or the Between function.
The like function
Like "249." & "*"
or
Like "*" & "249." & "*"
would provide all values starting with 249.
The Between function might work with Text so
Between "630" and "680"
would give any values between those 2.
If it doesn't work with text you might have to convert the text to a Value where possible.

Can you provide a database or excel sheet with some examples of those ranges to do some testing with?

[mpearce]

The between function does indeed work with text and that seems to have done the trick. however this leaves me with a very long query that i would ultimately like to store as the rowsource for a listbox. Here it is:

"SELECT WAH.* FROM WAH WHERE (((WAH.[Final DX1]) Between '42' And '42.99')) OR (((WAH.[Final DX1]) Between '140' And '239.99')) OR (((WAH.[Final DX1]) Between '249' And '249.99')) OR (((WAH.[Final DX1]) Between '250' And '250.99')) OR (((WAH.[Final DX1]) Between '277.30' And '277.39')) OR (((WAH.[Final DX1]) Between '278.01' And '278.01')) OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) OR (((WAH.[Final DX1]) Between '295' And '300.99')) OR (((WAH.[Final DX1]) Between '318' And '319')) OR (((WAH.[Final DX1]) Between '330' And '337.99')) OR (((WAH.[Final DX1]) Between '340' And '349.99')) OR (((WAH.[Final DX1]) Between '357.00' And '357.99')) OR (((WAH.[Final DX1]) Between '359.00' And '359.99')) OR (((WAH.[Final DX1]) Between '369.40' And '369.49')) OR (((WAH.[Final DX1]) Between '389.00' And '389.03')) OR (((WAH.[Final DX1]) Between '389.70' And '389.79')) OR (((WAH.[Final DX1]) Between '403' And '404.99')) OR (((WAH.[Final DX1]) Between '410' And '414.99')) OR (((WAH.[Final DX1]) Between '416.80' An
d '416.99')) OR (((WAH.[Final DX1]) Between '428' And '428.99')) OR (((WAH.[Final DX1]) Between '443' And '443.99')) OR (((WAH.[Final DX1]) Between '446' And '446.99')) OR (((WAH.[Final DX1]) Between '491.20' And '491.29')) OR (((WAH.[Final DX1]) Between '492' And '492.99')) OR (((WAH.[Final DX1]) Between '493.20' And '493.29')) OR (((WAH.[Final DX1]) Between '501' And '501.99')) OR (((WAH.[Final DX1]) Between '505' And '505.99')) OR (((WAH.[Final DX1]) Between '514' And '514.99')) OR (((WAH.[Final DX1]) Between '519' And '519.99')) OR (((WAH.[Final DX1]) Between '571' And '571.99')) OR (((WAH.[Final DX1]) Between '581' And '581.99')) OR (((WAH.[Final DX1]) Between '585' And '585.99')) OR (((WAH.[Final DX1]) Between '586' And '586.99')) OR (((WAH.[Final DX1]) Between '710' And '710.99')) OR (((WAH.[Final DX1]) Between '714' And '715.99')) OR (((WAH.[Final DX1]) Between '722' And '722.99')) OR (((WAH.[Final DX1]) Between '724' And '724.99')) OR (((WAH.[Final DX1]) Between '737' And '737.99')) OR (((WAH.[Final
DX1]) Between '741' And '742.99')) OR (((WAH.[Final DX1]) Between '784' And '784.99')) OR (((WAH.[Final DX1]) Between '997.60' And '997.69')) OR (((WAH.[Final DX1]) Between 'v21.30' And 'v21.39')) OR (((WAH.[Final DX1]) Between 'v42' And 'v42.99')) OR (((WAH.[Final DX1]) Between 'v45.10' And 'v45.19')) OR (((WAH.[Final DX1]) Between 'v53.80' And 'v53.89')) OR (((WAH.[Final DX1]) Between 'v62.84' And 'v62.84')) ORDER BY WAH.[Final DX1];"

Obviously that uses multiple lines in the VB editor but i cant figure out how to add separators so that it will treated as one line even though it is multiple lines. any ideas?

Also i need to use the where clause as criteria for a dsum and dcount statement. Unless there is a better way to sum a charge field meeting the where clause and then count the number of results.

[CreganTur]

Obviously that uses multiple lines in the VB editor but i cant figure out how to add separators so that it will treated as one line even though it is multiple lines. any ideas?

You'll need to put ending quotation marks at the end of the line, then use the underscore character to tell VBA you're moving to a new line. You'll need to use an anpersand to concatenate the multiple strings... like this:

[vba]"SELECT WAH.* FROM WAH WHERE (((WAH.[Final DX1]) Between " _
& "'42' And '42.99')) OR (((WAH.[Final DX1]) Between '140' And '239.99')) " _
& "OR (((WAH.[Final DX1]) Between '249' And '249.99')) OR (((WAH.[Final " _
& "DX1]) Between '250' And '250.99')) OR (((WAH.[Final DX1]) Between " _
& "'277.30' And '277.39')) OR (((WAH.[Final DX1]) Between '278.01' And " _
& "'278.01')) OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) OR " _
& "(((WAH.[Final DX1]) Between '295' And '300.99')) OR (((WAH.[Final " _
& "DX1]) Between '318' And '319')) OR (((WAH.[Final DX1]) Between '330' " _
& "And '337.99')) OR (((WAH.[Final DX1]) Between '340' And '349.99')) OR " _
& "(((WAH.[Final DX1]) Between '357.00' And '357.99')) OR (((WAH.[Final " _
& " ,etc."[/vba]

obviously, you'll probably want to make your splits in places that make sense, instead of randomly like I did above.

HTH

[mpearce]

You'll need to put ending quotation marks at the end of the line, then use the underscore character to tell VBA you're moving to a new line. You'll need to use an anpersand to concatenate the multiple strings

That works to a point, but i eventually get "too many line continuations"

[OBP]

Can I ask why you are doing it in VBA rather than having a standalone Query?

[mpearce]

Can I ask why you are doing it in VBA rather than having a standalone Query?

I need the results in a listbox and i thought VBA was the only way to set the rowsource of a listbox with a query. I mean i know you can set the row source in the properties pane of the listbox.

Basically i would like to have that query run and populate a listbox with the results.

If there is a way to do that with a standalone query, then i would love to know how that is done.

[CreganTur]

Just set the list box's row source to the name of the query you created. And make sure the row source type is table/query.

[OBP]

You might need to requery it if data is changed.

[mpearce]

I have it working now thanks a lot guys.

how can i have a query display only columns that contain data? I know that i can choose the columns in the query and accomplish it that way. basically i will be populating certain fields with data, the fields that i use may be different from time to time. This is all dependent on what the the client gives me to analyze.

Do this make sense?

[CreganTur]

how can i have a query display only columns that contain data? I know that i can choose the columns in the query and accomplish it that way. basically i will be populating certain fields with data, the fields that i use may be different from time to time. This is all dependent on what the the client gives me to analyze.

If I understand your question correctly, you can accomplsh this by putting a test to see if the field is null in the WHERE clause. Something like:
[VBA]WHERE TableName.FieldName IS NOT NULL[/VBA]

HTH

[OBP]

I would leave all of the fields in the Query and display the results on a form, where you can make the Fields visible or not depending on whether or not there is data. (But not in Datasheet or Continuous Forms mode).
Randy's Criteria will filter out Any Records where that field has no data, not just the field.
You could use your original VBA SQL code to write a QueryDef and only change it when required, you can even use a Field selection process for including the Fields or not.
There has been a Thread on here previously about Interactive SQL.

[mpearce]

i think some of you have hit the nail on this.

but to further explain here is what i am thinking

something like:

if (dcount(field 1) > 0) or (dcount(field 2) > 0) ... (dcount(field n) > 0) then

'add to select query

else

'do not include in select query


I'd like to have this occur in a way where i can just write the code and not have to tweak it. Sort of a set it and forget it type thing. I could do this with a where clause but then i think i would have to tweak the query each time i load data into the table.

[OBP]

You would have to run the Query with all fields and then have the VBA modify it on the Fly and run it again in the modified version.
Is it worth it, for not seeing some empty Fields?

[mpearce]

It would mainly be for aesthetics i suppose, maybe for readability if i were to export the results to excel or some other form. I probably have about 60 fields in the table. I stretched the listbox across the entire width of the form even with it that wide only 13 of the fields are visible and there may be data in the fields that are not visible.

another thought:

how can i set the column width for all columns on a listbox to auto? I know i can adjust the width column by column and take a guess at a number, but i was hoping there was a better way to do that

[OBP]

Sorry, I can't help, I never use List boxes.

[Norie]

Why do you have 60 fields?

Having that no of fields, some of which may be empty, points towards the design of your tables needing re-structured.

As for constructing your SQL string try this.
[vba]
strSQL = "SELECT WAH.* FROM WAH "
strSQL = strSQL & "WHERE (((WAH.[Final DX1]) Between '42' And '42.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '140' And '239.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '249' And '249.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '250' And '250.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '277.30' And '277.39')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '278.01' And '278.01')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '282.62' And '282.69')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '295' And '300.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '318' And '319')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '330' And '337.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '340' And '349.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '357.00' And '357.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '359.00' And '359.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '369.40' And '369.49')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '389.00' And '389.03')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '389.70' And '389.79')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '403' And '404.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '410' And '414.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '416.80'And '416.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '428' And '428.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '443' And '443.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '446' And '446.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '491.20' And '491.29')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '492' And '492.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '493.20' And '493.29')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '501' And '501.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '505' And '505.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '514' And '514.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '519' And '519.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '571' And '571.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '581' And '581.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '585' And '585.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '586' And '586.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '710' And '710.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '714' And '715.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '722' And '722.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '724' And '724.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '737' And '737.99')) "
strSQL = strSQL & "OR (((WAH.[FinalDX1]) Between '741' And '742.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '784' And '784.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between '997.60' And '997.69')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v21.30' And 'v21.39')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v42' And 'v42.99')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v45.10' And 'v45.19')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v53.80' And 'v53.89')) "
strSQL = strSQL & "OR (((WAH.[Final DX1]) Between 'v62.84' And 'v62.84')) "
strSQL = strSQL & "ORDER BY WAH.[Final DX1];"
[/vba]
I would also be careful using Between with text values, it could cause problems similar to those when you try and sort data with text and numbers.

[mpearce]

Why do you have 60 fields?

Some of the files i have analyze contain that many fields

[mpearce]

I have an idea in my head I am just not sure how to apply it. So here goes:

I have a file that I am importing into access that contains medical data. It can have up to 6 diagnosis codes. I have 3 categories of codes and i need to search the table for those codes. A code can appear in any one of the 6 diagnosis code fields. I have priorities for the categories of diagnosis codes. Top priority is called OB, 2nd to that is Disability, 3rd to that is TPL.

I need a way to catch all OB codes from the 6 fields. What i was doing before was by hand i was sorting by diag code 1 and then doing a cut and paste on the data i need, and then deleting the records i just pasted from the original table. If the record contains a OB and a disability code this needs to be pulled as a part of the OB search because OB takes priority.

So i need to be removing records as a go along so that a record isnt placed somewhere twice.

So i was thinking of having a make table query handle OB codes appearing in the diag code 1 field, but then how would i delete those results from the original table? In addition to the make table i would then have append queries that would handle the rest of the diag codes. Again question being how can i remove the results of each query from the original table?

Does this make sense? Any help would be greatly appreciated.

[Norie]

I think you need to have a bit of a rethink on the whole thing.

For a start why not create a table with all the possible diagnosis codes and their categories?

Then you could use that in your query(s) when you wanted to extract data for a particular category.

The next thing you should look into is restructuring the data.

Having even 6 fields that are basically the same for one record isn't the best way to go about things.

I don't know what type of data you are dealing with but I think you probably need to separate the diagnosis codes out into another table.

You can link the original table and the new table by creating a primary key in the first and using it as a foreign key in the 2nd.

This probably all sounds like a lot of work, but believe me it would be worth it in the long run.

If you can post some sample data, then I could perhaps illustrate what I'm suggesting.

PS Obviously don't post 'real' data, just post something that is representative.