what is the criteria to look up more than 1 Value in a field

[Angela]

In my Access Contacts Database I have a field called Counties and would
like to look up more than one county at a time .what would I type in the
criteria field of the query. So when asked the question how many solicitors
in Berkshire and Reading at present I have to do 2 searches

 

[Jeanette Cunningham]

Hi Angela,
"Berkshire" Or "Reading"

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[StrayBullet via AccessMonster.com]

"Berkshire" Or "Reading"

In my Access Contacts Database I have a field called Counties and would
like to look up more than one county at a time .what would I type in the
criteria field of the query. So when asked the question how many solicitors
in Berkshire and Reading at present I have to do 2 searches

 

[Angela]

Thank you so much so that means I would have to do a different query for each
Look up I am asked for?? I was hoping that I could type in something that
would look up any county that I typed in the Query

 

[Angela]

Thank you stray bullet, Is there any wat to chose any of the counties in the
list, as this would mean I would end up with hundreds of queries each time
someone asked me for info for different countis

Regards Angela

 

[Jeanette Cunningham]

You can use a form with a list of counties in a drop down.

In the query criteria you can put Forms!FormName!DropDownName

Replace FormName with the name of the form that has the drop down.
Replace DropDownName with the name of the dropdown.

After you choose a county from the drop down, run the query.
You can use the After Update event of the drop down to do that.

If you wish to search for several counties at the same time, you will need
to use a listbox in multi-select mode or several drop downs.

Here is a sample database that may help.
The site has a great many helpful sample databases.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=301

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

In my Access Contacts Database I have a field called Counties and would
like to look up more than one county at a time .what would I type in the
criteria field of the query. So when asked the question how many solicitors
in Berkshire and Reading at present I have to do 2 searches

IN ("Berkshire", "Reading")

will work if you're using the query grid. If you're using a Parameter query it
gets a bit more complicated - post back if so.

 

[Dale Fye]

As Jeanette mentioned, you could use a combo box on a form to select a
"single" county, or you could use a listbox with the Multiselect property set
to "simple" or "extended". If you go this route, you would also need to have
a command button to execute the query, report, or to open a form with the
query results. The code in the click event of this command button would look
something like:

Private sub cmd_Report_Click

Dim strSQL as String, strCounties as string
dim varItem as variant

strSQL = "SELECT * FROM yourTABLE "
For each varItem in me.lst_Counties.ItemsSelected
strCounties = strCounties & "," & chr$(34) _
& me.lst_Counties.columns(0, varItem) _
& chr$(34)
Next varItem

Select Case me.lst_Counties.ItemsSelected
Case 0
'do nothing
Case 1
strSQL = strSQL & "WHERE [FieldName] = " _
& Mid(strCounties, 2)
Case Else
strSQL = strSQL & "WHERE [FieldName] IN(" _
& Mid(strCounties, 2) & ")"
End Select

'At this point, what you do with strSQL will be dependant
'on whether you are opening a query, a report or a form
Debug.print strSQL

end sub
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.