By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Multiple Values in Single Field

P: n/a
I have inherited a medical database in which there are multiple values
stored in a single field (ie. "Current Conditions" field might contain
1-20 different conditions, separated by comma (ie. "Heart
Disease,Hyper Tyroid,Cancer" etc.

I would like to search via combo box for any one or more than one
value in this field, ie-what patients have Heart Disease or Cancer by
selecting these disorders off the combo box list.

Any help would be greatly appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
rkc

"Jen F." <je****@hotmail.com> wrote in message
news:91**************************@posting.google.c om...
I have inherited a medical database in which there are multiple values
stored in a single field (ie. "Current Conditions" field might contain
1-20 different conditions, separated by comma (ie. "Heart
Disease,Hyper Tyroid,Cancer" etc.

I would like to search via combo box for any one or more than one
value in this field, ie-what patients have Heart Disease or Cancer by
selecting these disorders off the combo box list.


As a recordsource
SELECT * FROM PatientConditionsTable
WHERE [Current Conditions]
LIKE "*" & Me.cboCondition & "*"

or as a saved query
SELECT * FROM PatientConditionsTable
WHERE [Current Conditions]
LIKE "*" & [Forms]![frmConditions]![cboConditions] & "*"



Nov 12 '05 #2

P: n/a
Jen F. wrote:
I have inherited a medical database in which there are multiple values
stored in a single field (ie. "Current Conditions" field might contain
1-20 different conditions, separated by comma (ie. "Heart
Disease,Hyper Tyroid,Cancer" etc.

I would like to search via combo box for any one or more than one
value in this field, ie-what patients have Heart Disease or Cancer by
selecting these disorders off the combo box list.

Any help would be greatly appreciated.

RKC gave you the pointer to do it. Since you need multiple choices, I
would recommend that you create a filter string in your form and I would
not use a combo box. I would use a list box, set the listbox to type
Simple so that you can make multiple selections. Then run thru the list
of selected items. This is aircode but may help you out. I will use
the word ListBox to refer to your listbox name. It assumes column 0
where the name is held CC is current conditions

Sub AirCode
If Me.Listbox.itemsselected.count > 0 then
'make sure something is selected
Dim var As Variant
Dim sf As String 'string filter
for each var in Me.Listbox.ItemsSelected
'create a list of each
sf = sf & "([CC] LIKE *" & Me.ListBox.Column(0,var) & "*) Or "
next
sf = Left(sf,len(sf) - 4) 'remove the word OR
Me.Filter = sf
Me.FilterOn = True
else
msgbox "Please select the condition to check for:
endif
End Sub

You now have the sf string you can use in a query, a form, a report,
whatever.

Please see online help in Access for properties, methods, and functions
you may not be familiar with in the above code.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.