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 )



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

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

'
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


Harry


On Fri, 18 Feb 2005 15:40:23 -0500, "Tom Ogilvy" wrote:

Unless the last name is listed in a cell, you wouldn't dimension UserRange
as a range

Sub GetUserRange()
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



and

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

--
Regards,
Tom Ogilvy