Excel VBA putting text from textbox into SQL statement
 

Hi all, I have an SQL statement which I would like to have a variable
"WHERE" section. For example:

SELECT blah blah blah
WHERE DATA = variabledata.text


(where the variabledata.text is text from a text box.) Is this possible
to do?

Basically, the spreadsheet opens and asks for a "period" which is a
number between 1 and 3. The user then puts this into the inputbox and I
want that text to go into the variabledata.text part of the statement.

I hope this is enough info!

Any help is much appretiated,

Regards,

Jon.

Excel VBA putting text from textbox into SQL statement
 

Jon,
Try something like


Dim SQL As String
SQL = "SELECT blah blah blah " & _
"WHERE DATA = `" & userform1.TextBox.Text & "'"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



wrote in message
ps.com...
Hi all, I have an SQL statement which I would like to have a variable
"WHERE" section. For example:

SELECT blah blah blah
WHERE DATA = variabledata.text


(where the variabledata.text is text from a text box.) Is this possible
to do?

Basically, the spreadsheet opens and asks for a "period" which is a
number between 1 and 3. The user then puts this into the inputbox and I
want that text to go into the variabledata.text part of the statement.

I hope this is enough info!

Any help is much appretiated,

Regards,

Jon.

Excel VBA putting text from textbox into SQL statement
 

You also might want to look into parameter queries:

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

I don't know if this is applicable to what you are doing, but it might be.

--
Regards,
Tom Ogilvy


" wrote:

Hi all, I have an SQL statement which I would like to have a variable
"WHERE" section. For example:

SELECT blah blah blah
WHERE DATA = variabledata.text


(where the variabledata.text is text from a text box.) Is this possible
to do?

Basically, the spreadsheet opens and asks for a "period" which is a
number between 1 and 3. The user then puts this into the inputbox and I
want that text to go into the variabledata.text part of the statement.

I hope this is enough info!

Any help is much appretiated,

Regards,

Jon.

Excel VBA putting text from textbox into SQL statement
 

Hi Chip, many thanks for your reply. What you are saying does make
sense and I have tried it as you suggested but I get the compile error
"Expected list seperator or )" I have pasted my code below if that
helps?

Regards, Jon.



Sub Period_Selection2()
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER=SQL
Server;SERVER=MYSERVER;UID=odbcacc;PWD=;APP=Micros oft Office
XP;WSID=ACC-020;DATABASE=PassMain;Network=DBMSSOCN;Ad" _
), Array("dress=MYSERVER.MYDOMAIN.COM,1433"))
.CommandText = Array( _
"SELECT ""_NOMINAL_TRANSACTIONS"".PERIOD,
""_NOMINAL_TRANSACTIONS"".BATCH_NUMBER AS 'BATCH',
""_NOMINAL_TRANSACTIONS"".TRANSACTION_DATE AS 'DATE',
""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE AS 'NOMINAL', ACCOUNT.D" _
, _
"ESCRIPTION, ""_NOMINAL_TRANSACTIONS"".REFERENCE_1,
""_NOMINAL_TRANSACTIONS"".REFERENCE_2,
""_NOMINAL_TRANSACTIONS"".TRANSACTION_VALUE AS 'VALUE'" & Chr(13) & ""
& Chr(10) & "FROM PassMain.dbo.""_NOMINAL_TRANSACTIONS""
""_NOMINAL_TRANSACT" _
, _
"IONS"", PassMain.dbo.ACCOUNT ACCOUNT" & Chr(13) & "" & Chr(10)
& "WHERE ""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE = ACCOUNT.CODE AND
((""_NOMINAL_TRANSACTIONS"".YEAR=2007) AND
(""_NOMINAL_TRANSACTIONS"".DEPARTMENT_CODE='S3X C') AND (""_NOMI" _
, _
"NAL_TRANSACTIONS"".TRANSACTION_VALUE Not Between -0.01 And
0.01) AND (""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE Between 'A790' And
'B999') AND (""_NOMINAL_TRANSACTIONS"".PERIOD = '"&
UserForm1.TextBox1.Text"'))" _
)
.Refresh BackgroundQuery:=True
End With
End Sub












Chip Pearson wrote:
Jon,
Try something like


Dim SQL As String
SQL = "SELECT blah blah blah " & _
"WHERE DATA = `" & userform1.TextBox.Text & "'"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



wrote in message
ps.com...
Hi all, I have an SQL statement which I would like to have a variable
"WHERE" section. For example:

SELECT blah blah blah
WHERE DATA = variabledata.text


(where the variabledata.text is text from a text box.) Is this possible
to do?

Basically, the spreadsheet opens and asks for a "period" which is a
number between 1 and 3. The user then puts this into the inputbox and I
want that text to go into the variabledata.text part of the statement.

I hope this is enough info!

Any help is much appretiated,

Regards,

Jon.

Excel VBA putting text from textbox into SQL statement
 

UserForm1.TextBox1.Text"'))"

as a minimum should be

UserForm1.TextBox1.Text & "'))"

--
Regards,
Tom Ogilvy


" wrote:

Hi Chip, many thanks for your reply. What you are saying does make
sense and I have tried it as you suggested but I get the compile error
"Expected list seperator or )" I have pasted my code below if that
helps?

Regards, Jon.



Sub Period_Selection2()
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER=SQL
Server;SERVER=MYSERVER;UID=odbcacc;PWD=;APP=Micros oft Office
XP;WSID=ACC-020;DATABASE=PassMain;Network=DBMSSOCN;Ad" _
), Array("dress=MYSERVER.MYDOMAIN.COM,1433"))
.CommandText = Array( _
"SELECT ""_NOMINAL_TRANSACTIONS"".PERIOD,
""_NOMINAL_TRANSACTIONS"".BATCH_NUMBER AS 'BATCH',
""_NOMINAL_TRANSACTIONS"".TRANSACTION_DATE AS 'DATE',
""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE AS 'NOMINAL', ACCOUNT.D" _
, _
"ESCRIPTION, ""_NOMINAL_TRANSACTIONS"".REFERENCE_1,
""_NOMINAL_TRANSACTIONS"".REFERENCE_2,
""_NOMINAL_TRANSACTIONS"".TRANSACTION_VALUE AS 'VALUE'" & Chr(13) & ""
& Chr(10) & "FROM PassMain.dbo.""_NOMINAL_TRANSACTIONS""
""_NOMINAL_TRANSACT" _
, _
"IONS"", PassMain.dbo.ACCOUNT ACCOUNT" & Chr(13) & "" & Chr(10)
& "WHERE ""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE = ACCOUNT.CODE AND
((""_NOMINAL_TRANSACTIONS"".YEAR=2007) AND
(""_NOMINAL_TRANSACTIONS"".DEPARTMENT_CODE='S3X C') AND (""_NOMI" _
, _
"NAL_TRANSACTIONS"".TRANSACTION_VALUE Not Between -0.01 And
0.01) AND (""_NOMINAL_TRANSACTIONS"".ACCOUNT_CODE Between 'A790' And
'B999') AND (""_NOMINAL_TRANSACTIONS"".PERIOD = '"&
UserForm1.TextBox1.Text"'))" _
)
.Refresh BackgroundQuery:=True
End With
End Sub












Chip Pearson wrote:
Jon,
Try something like


Dim SQL As String
SQL = "SELECT blah blah blah " & _
"WHERE DATA = `" & userform1.TextBox.Text & "'"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



wrote in message
ps.com...
Hi all, I have an SQL statement which I would like to have a variable
"WHERE" section. For example:

SELECT blah blah blah
WHERE DATA = variabledata.text


(where the variabledata.text is text from a text box.) Is this possible
to do?

Basically, the spreadsheet opens and asks for a "period" which is a
number between 1 and 3. The user then puts this into the inputbox and I
want that text to go into the variabledata.text part of the statement.

I hope this is enough info!

Any help is much appretiated,

Regards,

Jon.