Using an Excel list as criteria in a query

[Doug K]

I have a large list of user names in an excel spreadsheet
that I want to use as a criteria in a query. Is there an
easy way to get this list in from Excel to the criteria
field in my query? The query criteria field has a specific
format which is

like "Jones (Bob)" or like "Smith (John)" etc

and my Excel list is in the format of Smith (John).

Any way to do this?

thanks,

Doug K

 

[Tom Ellison]

I have a large list of user names in an excel spreadsheet
that I want to use as a criteria in a query. Is there an
easy way to get this list in from Excel to the criteria
field in my query? The query criteria field has a specific
format which is

like "Jones (Bob)" or like "Smith (John)" etc

and my Excel list is in the format of Smith (John).

Any way to do this?

thanks,

Doug K

Dear Doug:

Create a linked table that is the spreadsheet and perform an inner
join on the columns that form the criteria.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

I have a large list of user names in an excel spreadsheet
that I want to use as a criteria in a query. Is there an
easy way to get this list in from Excel to the criteria
field in my query? The query criteria field has a specific
format which is

like "Jones (Bob)" or like "Smith (John)" etc

The LIKE operator is inappropriate here: LIKE uses wildcards such as #
to match any digit or * to match any string of characters. It sounds
like you want to retrieve records which match exactly - so you don't
need LIKE.

and my Excel list is in the format of Smith (John).

Is this how the names are stored in your Access table? If so, you
could use the IN() SQL operator. Use File... Get External Data... Link
to link to the spreadsheet; I'll assume that the resulting linked
pseudotable is called Sheet1 and has only one field named A containing
these names.

You can then use

WHERE [namefield] IN(SELECT [A] FROM Sheet1])

or even create a Query joining your table to Sheet1, joining the name
field to [A].

If your table contains a LastName "Smith" and a FirstName "John" then
of course this will require more work. Access is a computer program,
not a miracle worker - if you ask it to search for "Smith (John)" then
it will look for a twelve-byte string containing a blank, two
parentheses, and the letters in that order, not for anything else.