RTP,
Beacuse your variables are currently within the string, they are not being
seen as variable by VBA and hence not evaluated. So if you do:
Debug.Print "WHERE (Access_data.NAME=v_name AND Access_data.LOCATION=v_loc)"
you get:
WHERE (Access_data.NAME=v_name AND Access_data.LOCATION=v_loc)
Which is not what you are after.
So, you have to end your string, add the value of the variable, continue
with the string:
"WHERE (Access_data.NAME=" & v_name & " AND Access.....
But you also need to add the " around the variable as Access expects string
to be surrounded by quotes (or single quotes ', Chr(39)). So:
"WHERE (Access_data.NAME=" & Chr(34) & v_name & Chr(34) & " AND Access.....

NickHK
P.S. Notice I dropped the bracket and comma, which I suppose comes from the
Array(....) that VBA generates for these queries, but is not usually
neccessary, unless your SQL exceeds the limit (1024 characters ??)
Also, I tend to avoid giving fields names that may conflict with some
property etc that is used. Name, Date, Count, Item etc can be used in Access
(e.g. as [Date]), but to avoid confusion, of yourself and the software, use
names that are more descriptive.


"RTP" wrote in message
...
I am trying to import external data into Excel from Access using the
database
query technique. I have recorded a macro that pulls in data based on
specific criteria, the following is the criteria I have used:


"WHERE (Access_data.NAME='Mr Smith') AND " ,
"(Access_data.LOCATION='UK')")

This works sucessfully (and returns four records) but what I need to do is
replace the 'hard coded' criteria with varaibles, like this:

v_name = "Mr Smith"
v_loc = "UK"

"WHERE (Access_data.NAME=v_name AND " , "(Access_data.LOCATION=v_loc)")

This method does not return any records becasue I am using variables. If
I
hard-code the variables the query works.

How can I successfully get external data from an Access database using
variables as selection criteria?

Thanks
RTP