Updating a text field from a list box and then filtering the results in a report

[datasec23]

Hi...

I really appreciate your help with my question, I am pulling my hair out on this. I have not been able to find an answer on the other posts.

I am using Access 2000. I have a form that has a list box that pulls a full list of medicines from a table. In the table I have two columns Medicine Name and Show When. I only show Medicine Name in the list box on the form. On the form I also have a text box (named Medicine), that is populated when a medicine name is selected from the list box. Each time a medicine is selected, it adds the name to the text box (Medicine), seperated by semicolons.

I am now trying to create a report that has the field Medicine (populated by the list box and seperated by semicolons) on it, but I only want to show the medicines that have a specific corresponding letter in the Show When field of the table.

I have two problems, how do I just pull out individual medicine names from the Medicine field (text box) and how do I only show ones with the correct corresponding letter?

I thought about having the list box populate another table with the medicines, and have a datasheet in a subform on the form, but I can't even begin to figure out how to do that.

Sorry for the long post from a frustrated newbie....and thanks so much for your help.

[Rabbit]

Your tables aren't normalized. They should be designed like:

tbl_Medicine
MedicineID; PK
MedicineName
ShowWhen

tbl_Patient
PatientID; PK
TreatmentDate; PK
PatientName
PatientAddress

tbl_PatientMedicine
PatientID; FK
TreatmentDate; FK
MedicineID; FK

And you'll have multiple records for each patient.
With the tables designed like this, the query becomes easy to make.