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.

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.

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.

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.

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.