Finding 'Like' data

[tacieslik]

How do I refer to another field when using the 'Like' operator? I want a query to look at the value in a textbox and then display all the records in a table that contain say the first 5 characters that are the same as the text fields?

 

[Mile-O]

[AnotherField] Like "*TestData*"

 

[dcx693]

If you want to compare the first 5 characters of your table data to the first 5 of what the user has entered into a text box on a form:

Create a calculated field in your query, placing something like this in the "Field" line:
Left([your_field],5)

In the "Critieria" line for that calculated field you want to search, refer to the text control that's on the form where the user has entered data like this:
Like Left(Forms!form_name!form_field,5) & "*"

 

[tacieslik]

Thanks to both of you. I think I'm going to have to be a bit cleverer than I originally thought!
The problem in more detail is this:

I have a list of drugs in a table called tblDrugs. I also have a form and sub form that display records that come from an external source. The user selects a record from the sub form and presses a button. A query is then run to see if the drug that they have selected exists in the tblDrugs table. If not, it will display a message and give them the option to add the drug to the tblDrugs table. Now my problem starts.
I want the user to be told that the drug does not exist. I then want them to have a choice of adding a new drug (this bit is easy) and then also have a button to Update a current record in the tblDrugs table. If the second button is clicked, I need a query to look at the tblDrugs table and show all the drugs that are similar to those asked for. The data I have to cope with is like this:

Data in the tblDrugs table:

fldDrugName
ABIDEC ORAL DROPS
ACEPRIL 12.5MG
ACEPRIL 25MG
ACETOXYL GEL 2.5%
ACHROMYCIN 250MG

Sample Data asked for:
fldDrugName
ABIDEC DROPS ORAL
ACEPRIL 12.5MG Tablets
ACEPRIL 25MG
ACETOXYL 2.5% GEL
ACHROMYCIN 250MG Cream


Have you got any suggestions on how I could cope with such a variation of data?

 

[Mile-O]

Write a function rather than just use criteria. If you have Access 2000 or above you can use the Split function to break the test data down and then query it all.

Access 97 you've have to write your own but I've written a few and posted them on here before.

 

[tacieslik]

Thanks Mile-0-Phile

 

[dcx693]

Quality of data is important. If that's really how your current data is stored, I'd suggest permanently breaking it into drug name, dosage, form, and whatever else is relevant. Then you can more easily query the individual parts of the record. This might be a massive undertaking though, depending on how much data you've got.

 

[tacieslik]

Thanks dcx693,

I wish I could do this. Unfortunately I'm stuck with the format that the data is already in.