Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and MS Query
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and MS Query
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 "Confused Slug" wrote: | 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |