Tom

Before doctoring, the code generated by the macro recorder looked like this:

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

so I changed it to:

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

but I've also tried it as you gave it to me without the extra opening bracket::

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


In example number 1, it returns the proper record from the Access database, no matter what name I respond to the prompt with. That's as it should be, as the value of Applicant Last Name is set to "Fine"

In example number 2, it prompts me for the last name, but then doesn't retieve any data.

In example 3, it prompts me for the last name, but then doesn't retieve any data.


In #3, the one you suggested, you've used the ampersands to ensure it is seen as a variable, not a literal, and for some reason you've added a closing bracket at the end of the line, even though it isn't matched by an opening
bracket.

That's why I tried number 2, adding an opening bracket before the variable, but it didn't make a difference, it still doesn't return the data from Excel. So...to answer your question, before I doctored it, it looked like your
third example below.

I'm stuck.

Harry


On Mon, 21 Feb 2005 13:05:28 -0500, "Tom Ogilvy" wrote:

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 )