Excel and MS Query

[Guest]

Is it possible to set the value of the 'value' criteria with MSQuery using
data entered into a UserForm in Excel?

What i would like to do is to have a variable for the Like value in MS query
and the value of this variable is set in a UserForm in excel.

I have a macro which displays a UserForm into which i would like the enter
the criteria. The macro then runs the desired MS Query but i am unable to
refine the query using the information entered in to the Textbox. When i use
this variable to define the 'Where' part of the 'Select' 'From' 'Where'
function the Query errors.

 

[Dave Patrick]

You might try something like this.

Private Sub CommandButton1_Click()
If Len(Trim(TextBox1)) > 0 And Not IsNull(TextBox1) Then
Set qt = Worksheets(1).QueryTables(1)
With qt
.CommandType = xlCmdSql
.CommandText = _
"SELECT ContactID, NameStyle, Title, " _
& "FirstName, MiddleName, LastName, " _
& "Suffix, EmailAddress, EmailPromotion, " _
& "Phone, PasswordHash, PasswordSalt, " _
& "AdditionalContactInfo, rowguid, ModifiedDate " _
& "FROM AdventureWorks.Person.Contact Contact " _
& "WHERE (LastName Like '" & Trim(TextBox1) & "%') "
.Refresh
End With
End If
Unload Me
End Sub

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Is it possible to set the value of the 'value' criteria with MSQuery using
| data entered into a UserForm in Excel?
|
| What i would like to do is to have a variable for the Like value in MS
query
| and the value of this variable is set in a UserForm in excel.
|
| I have a macro which displays a UserForm into which i would like the enter
| the criteria. The macro then runs the desired MS Query but i am unable to
| refine the query using the information entered in to the Textbox. When i
use
| this variable to define the 'Where' part of the 'Select' 'From' 'Where'
| function the Query errors.