I want retreive data from Table1 from a SQL database which match the WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')

date1 is a field in Excel which I use to define the date for the WHERE
criteria.

I tried the following but it did not work (I entered the date value in field
A1 in the Excel sheet):
SELECT *
FROM Table1
WHERE ('A1'='07/07/2005')

What am I doing wrong?
Thanks

You don't say how you are trying to retrieve this data, but since this
is a programming forum I'll assume you're trying to use VBA. In that case,
you'll need to specify the SQL statement as a string in order to do anything
with it. One way to do that, which includes a demonstration of how to
collect the date from your worksheet cell, is the following:

Dim szSQL As String

szSQL = "SELECT * " & _
"FROM Table1 " & _
"WHERE (" & Sheet1.Range("A1").Value & "='07/07/2005')"

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
I want retreive data from Table1 from a SQL database which match the WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')

date1 is a field in Excel which I use to define the date for the WHERE
criteria.

I tried the following but it did not work (I entered the date value in
field
A1 in the Excel sheet):
SELECT *
FROM Table1
WHERE ('A1'='07/07/2005')

What am I doing wrong?
Thanks

Zinewowe wrote:
I want retreive data from Table1 from a SQL database which match the WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')


In the database e.g. SQL Server

CREATE PROCEDURE SillyNameProc
@date1 DATETIME = NULL
AS
SELECT * -- don't use * in production code!
FROM Table1 -- silly name!
WHERE date1 = COALESCE(@date1, date1)

In the client e.g. Excel

"EXECUTE SillyNameProc " & _
Format$(Sheet1.Range("A1").Value, "YYYY-MM-DD")

Hi,
I have used VB before to retrieve data from a SQL database by using a string.
All want to do here is using Microsoft Query to retrieve data from a SQL
database into Micorsoft Excel.

Do do so I entered the following string into the Microsoft query SQL
statement editor:
SELECT *
FROM table1
WHERE (table1.date='A1')

A1 is simply the field in the Excel speadsheet which I would like to use to
difine the date which is then used in the WHERE statement.
So what I am doning is trying to pass on a parametervalue from the A1-field
to the SQL statement.
Obviously something with 'A1' (how I pass on the parameter) is wrong.

Thanks once more!





"Rob Bovey" wrote:


You don't say how you are trying to retrieve this data, but since this
is a programming forum I'll assume you're trying to use VBA. In that case,
you'll need to specify the SQL statement as a string in order to do anything
with it. One way to do that, which includes a demonstration of how to
collect the date from your worksheet cell, is the following:

Dim szSQL As String

szSQL = "SELECT * " & _
"FROM Table1 " & _
"WHERE (" & Sheet1.Range("A1").Value & "='07/07/2005')"

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
I want retreive data from Table1 from a SQL database which match the WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')

date1 is a field in Excel which I use to define the date for the WHERE
criteria.

I tried the following but it did not work (I entered the date value in
field
A1 in the Excel sheet):
SELECT *
FROM Table1
WHERE ('A1'='07/07/2005')

What am I doing wrong?
Thanks

In order to do this with MS Query you have to 1) create a parameter
query, 2) provide the initial parameter manually, 3) dump the data back to
Excel, then 4) edit the query to point the parameter at your source data
cell.

The SQL for the parameter query looks like this:

SELECT *
FROM table1
WHERE (table1.date=?)

Note the question mark in the WHERE clause. When you enter this query, you
will be prompted for a parameter value. Give it some initial value and then
return the data to Excel.

Next, right-click on your data table and choose Parameters from the
shortcut menu. In the Parameters dialog select the "Get the value from the
following cell" option. Select the cell you want the value to come from and
optionally check the box that updates the data whenever that cell's value
changes, then click OK. Your query should now be connected to your input
cell.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
Hi,
I have used VB before to retrieve data from a SQL database by using a
string.
All want to do here is using Microsoft Query to retrieve data from a SQL
database into Micorsoft Excel.

Do do so I entered the following string into the Microsoft query SQL
statement editor:
SELECT *
FROM table1
WHERE (table1.date='A1')

A1 is simply the field in the Excel speadsheet which I would like to use
to
difine the date which is then used in the WHERE statement.
So what I am doning is trying to pass on a parametervalue from the
A1-field
to the SQL statement.
Obviously something with 'A1' (how I pass on the parameter) is wrong.

Thanks once more!





"Rob Bovey" wrote:


You don't say how you are trying to retrieve this data, but since
this
is a programming forum I'll assume you're trying to use VBA. In that
case,
you'll need to specify the SQL statement as a string in order to do
anything
with it. One way to do that, which includes a demonstration of how to
collect the date from your worksheet cell, is the following:

Dim szSQL As String

szSQL = "SELECT * " & _
"FROM Table1 " & _
"WHERE (" & Sheet1.Range("A1").Value & "='07/07/2005')"

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
I want retreive data from Table1 from a SQL database which match the
WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')

date1 is a field in Excel which I use to define the date for the WHERE
criteria.

I tried the following but it did not work (I entered the date value in
field
A1 in the Excel sheet):
SELECT *
FROM Table1
WHERE ('A1'='07/07/2005')

What am I doing wrong?
Thanks

Thanks a lot for the very useful information!

Unfortunately I ran into an additional problem:
My SQLdatabase has several relational tables.
Therfore some of the the queries I use can not be displayed qraphically by
MS query.
If I use a parameter in those queries I reveive the Error Message:
"Parameters are not allowd in queries that can't be displayed graphically."
What can I do here? Thanks!


"Rob Bovey" wrote:


In order to do this with MS Query you have to 1) create a parameter
query, 2) provide the initial parameter manually, 3) dump the data back to
Excel, then 4) edit the query to point the parameter at your source data
cell.

The SQL for the parameter query looks like this:

SELECT *
FROM table1
WHERE (table1.date=?)

Note the question mark in the WHERE clause. When you enter this query, you
will be prompted for a parameter value. Give it some initial value and then
return the data to Excel.

Next, right-click on your data table and choose Parameters from the
shortcut menu. In the Parameters dialog select the "Get the value from the
following cell" option. Select the cell you want the value to come from and
optionally check the box that updates the data whenever that cell's value
changes, then click OK. Your query should now be connected to your input
cell.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
Hi,
I have used VB before to retrieve data from a SQL database by using a
string.
All want to do here is using Microsoft Query to retrieve data from a SQL
database into Micorsoft Excel.

Do do so I entered the following string into the Microsoft query SQL
statement editor:
SELECT *
FROM table1
WHERE (table1.date='A1')

A1 is simply the field in the Excel speadsheet which I would like to use
to
difine the date which is then used in the WHERE statement.
So what I am doning is trying to pass on a parametervalue from the
A1-field
to the SQL statement.
Obviously something with 'A1' (how I pass on the parameter) is wrong.

Thanks once more!





"Rob Bovey" wrote:


You don't say how you are trying to retrieve this data, but since
this
is a programming forum I'll assume you're trying to use VBA. In that
case,
you'll need to specify the SQL statement as a string in order to do
anything
with it. One way to do that, which includes a demonstration of how to
collect the date from your worksheet cell, is the following:

Dim szSQL As String

szSQL = "SELECT * " & _
"FROM Table1 " & _
"WHERE (" & Sheet1.Range("A1").Value & "='07/07/2005')"

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Zinewowe" wrote in message
...
I want retreive data from Table1 from a SQL database which match the
WHERE
criteria:

SELECT *
FROM Table1
WHERE (date1='07/07/2005')

date1 is a field in Excel which I use to define the date for the WHERE
criteria.

I tried the following but it did not work (I entered the date value in
field
A1 in the Excel sheet):
SELECT *
FROM Table1
WHERE ('A1'='07/07/2005')

What am I doing wrong?
Thanks

"Zinewowe" wrote in message
...
Thanks a lot for the very useful information!

Unfortunately I ran into an additional problem:
My SQLdatabase has several relational tables.
Therfore some of the the queries I use can not be displayed qraphically by
MS query.
If I use a parameter in those queries I reveive the Error Message:
"Parameters are not allowd in queries that can't be displayed
graphically."
What can I do here? Thanks!

In that case, you won't be able to use MS Query. You'll have to write
VBA code to perform your data access.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm