Querying against a table. . .

[Guest]

I have a list (single column) of countries, Germany, Canada, France, etc, in
a table. Is there a way to Query those values to filter out "Berlin,
Germany", "montreal, Canada", etc from another, larger, table? Or do I need
to create a form and do a query by form, if so how does one get around the
issue of multi-select list boxes? Is there a way to multi-select everything
in a list with a macro/command button so that one doesn't have to select
everything in the list box everytime they want to run the query?

 

[Tom Ellison]

Dear anon:

Perhaps you could write a function that:

- Looks to see if there is a comma in the string

- removes the comma and everyting to the left

- strips all leading and trailing spaces

You could test this function by showing:

MyFunction(CityState)

In a test query on the values in that column of data. Does that look good?

When that looks right, try it in your finished query.

Perhaps you did not know you can use a custom function in a finished query.
Can be very useful.

Tom Ellison
Microsoft Access MVP

 

[John Spencer]

If you want to filter out the entire list

SELECT SomeTable.*
FROM SomeTable LEFT JOIN CountryTable
ON SomeTable.CityCountry Like CountryTable.Country & "*"
WHERE CountryTable.Country is Null

That presumes that your table and field names contain no spaces or other
characters that would require square brackets [] around the table and field
names.

If you want to use a multi-select list in a form then you would need to
build the query based on the selected items. And yes, you can use VBA to
select or deselect all the items in the multi-select list.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..