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 )