Before you doctored the code, did it look like

"WHERE (Customers.`Applicant Last Name`=Smith)"
or did it look like

"WHERE (Customers.`Applicant Last Name`=""Smith"")"

or perhaps with single quotes

"WHERE (Customers.`Applicant Last Name`='Smith')"

Whatever it looked like, all you want to do is replace the Smith part.
However, if double quotes are involved you will have to check to make sure
you have a legitimate string.

--
Regards,
Tom Ogilvy


"Harry" wrote in message
.. .
On Sat, 19 Feb 2005 11:32:20 -0500, "Tom Ogilvy" wrote:

I'm missing something Tom. It now compiles OK, and when I run it, but
clicking Tools/Macro/Macros/Run GetUserRange, it prompts for the Last Name
as expected, but then when I enter the last name FINE, which I've double
checked is
in the Access database, it doesn't seem to run the lower part of the
script. Nothing appears on the screen. No data is returned from Access.

Here's the whole script again, with the spaces around the & as you
suggested.

Thank you Tom.

Harry


Sub GetUserRange()
'
' Test Macro
' Macro recorded by Harry Fine
'
Dim UserRange As String
Prompt = "Select Last Name."
Title = "Select Last Name"

' Display the Input Box
On Error Resume Next
UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title)

' Was the Input Box canceled?
If UserRange = "" Then
MsgBox "Canceled."
Else

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\Harry\My
Documents\Landlord\Access\Clients.mdb;DefaultDir=C :\Documents and" _
), Array( _
" Settings\Harry\My Documents\Landlord\Access;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Customers.`Applicant FirstName`, Customers.`Applicant
Initital`, Customers.`Applicant Last Name`, Customers.`Applicant Gender`,
Customers.`Day Phone`, Customers.`Evening Phone`, Customers.`Stre" _
, _
"et Address`, Customers.`Unit #`, Customers.City,
Customers.Province, Customers.`Postal Code`, Customers.FaxNumber,
Customers.EmailAddress, Customers.`Second Applicant First Name`,
Customers.`Second Ap" _
, _
"plicant Initial`, Customers.`Second Applicant Last Name`,
Customers.`Second Applicant Gender`" & Chr(13) & "" & Chr(10) & "FROM
Customers Customers" & Chr(13) & "" & Chr(10) & "WHERE (Customers.`Applicant
Last Name`=" &
UserRange & ")")
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub




"WHERE (Customers.`Applicant Last Name`=" & UserRange & ")")

Put spaces before and after you ampersands.

--
Regards,
Tom Ogilvy


"Harry" wrote in message
. ..
I've cropped some of this thread off. Is this better? Damn line wraps
are awful. These underbars I see everywhere, are they to continue lines?
WHen I put in this:


"WHERE (Customers.`Applicant Last Name`="&UserRange&")")

I get an error. Expected: List separator or )