Hi folks,

I'm trying to create a little solution that will allow a user to open the
..xls and be prompted to input date values which would then be used to query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having trouble
passing my parameters through the SQL query, and I haven't even starting to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date =
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open the
.xls and be prompted to input date values which would then be used to query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having trouble
passing my parameters through the SQL query, and I haven't even starting to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date =
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL statement.
It was actualy two problems in one. First I needed to concat my statement
string with my variables "where oe_hdr.order_date = " & dtStartDate & " ...
Then I ran into the problem of datatype mismatchs which I was albe to fix by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back to my
sheet 1.

Any help is always appreciated.

Thanks!!!

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date =
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL statement.
It was actualy two problems in one. First I needed to concat my statement
string with my variables "where oe_hdr.order_date = " & dtStartDate & "
...
Then I ran into the problem of datatype mismatchs which I was albe to fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back to
my
sheet 1.

Any help is always appreciated.

Thanks!!!

MH,
Thanks for jumping in... I got what your saying about the BETWEEN being more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but how
do I tell it to equal the results of the Sql string?

"MH" wrote:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date =
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL statement.
It was actualy two problems in one. First I needed to concat my statement
string with my variables "where oe_hdr.order_date = " & dtStartDate & "
...
Then I ran into the problem of datatype mismatchs which I was albe to fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back to
my
sheet 1.

Any help is always appreciated.

Thanks!!!

'Dim the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
....
'Open the recordset here
rs.Open strSQL, dbConn
....
'Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing


I would use strSQL for your variable name instead of simply sql as this may
cause confusion later on.

HTH

MH

"Stephen" wrote in message
...
MH,
Thanks for jumping in... I got what your saying about the BETWEEN being
more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but
how
do I tell it to equal the results of the Sql string?

"MH" wrote:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from
taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and
then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open
the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some
more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even
starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE
order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL
statement.
It was actualy two problems in one. First I needed to concat my
statement
string with my variables "where oe_hdr.order_date = " & dtStartDate &
"
...
Then I ran into the problem of datatype mismatchs which I was albe to
fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back
to
my
sheet 1.

Any help is always appreciated.

Thanks!!!

ok, I've got the recordset and I know my date range contains data, I evne
have the code popupating the forst row with the field names, but for the life
of me I can't figure out why I'm not returning any data??

"MH" wrote:

'Dim the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
....
'Open the recordset here
rs.Open strSQL, dbConn
....
'Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing


I would use strSQL for your variable name instead of simply sql as this may
cause confusion later on.

HTH

MH

"Stephen" wrote in message
...
MH,
Thanks for jumping in... I got what your saying about the BETWEEN being
more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but
how
do I tell it to equal the results of the Sql string?

"MH" wrote:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from
taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and
then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open
the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some
more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even
starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE
order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL
statement.
It was actualy two problems in one. First I needed to concat my
statement
string with my variables "where oe_hdr.order_date = " & dtStartDate &
"
...
Then I ran into the problem of datatype mismatchs which I was albe to
fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back
to
my
sheet 1.

Any help is always appreciated.

Thanks!!!

Replace your # around the dates with '
As I recall ADO doesn't like #.

If that doesn't work then post your current code and the generated SQL statement.

--
Tim Williams
Palo Alto, CA


"Stephen" wrote in message ...
ok, I've got the recordset and I know my date range contains data, I evne
have the code popupating the forst row with the field names, but for the life
of me I can't figure out why I'm not returning any data??

"MH" wrote:

'Dim the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
....
'Open the recordset here
rs.Open strSQL, dbConn
....
'Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing


I would use strSQL for your variable name instead of simply sql as this may
cause confusion later on.

HTH

MH

"Stephen" wrote in message
...
MH,
Thanks for jumping in... I got what your saying about the BETWEEN being
more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but
how
do I tell it to equal the results of the Sql string?

"MH" wrote:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from
taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and
then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open
the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some
more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even
starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE
order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL
statement.
It was actualy two problems in one. First I needed to concat my
statement
string with my variables "where oe_hdr.order_date = " & dtStartDate &
"
...
Then I ran into the problem of datatype mismatchs which I was albe to
fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back
to
my
sheet 1.

Any help is always appreciated.

Thanks!!!

Fantastic, that did it!

"Tim Williams" wrote:

Replace your # around the dates with '
As I recall ADO doesn't like #.

If that doesn't work then post your current code and the generated SQL statement.

--
Tim Williams
Palo Alto, CA


"Stephen" wrote in message ...
ok, I've got the recordset and I know my date range contains data, I evne
have the code popupating the forst row with the field names, but for the life
of me I can't figure out why I'm not returning any data??

"MH" wrote:

'Dim the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
....
'Open the recordset here
rs.Open strSQL, dbConn
....
'Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing


I would use strSQL for your variable name instead of simply sql as this may
cause confusion later on.

HTH

MH

"Stephen" wrote in message
...
MH,
Thanks for jumping in... I got what your saying about the BETWEEN being
more
effiecient, thanks.

I create my recordset object
Set rst = New ADODB.Recordset
and I can see how to get it to return to the sheet via the VBA help, but
how
do I tell it to equal the results of the Sql string?

"MH" wrote:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
=
dtStartDate AND order_date <= dtEndDate"

The above would be more efficient using BETWEEN instead of lowest and <
highest, you have to include the hashes to stop the comparrison from
taking
your dates as strings:

Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
Between #" &
dtStartDate & "# AND #" & dtEndDate & "#"

Point 2.

You need to create a recordset object to store the returned records and
then
look at the CopyFromRecordset method in Excel VBA help.

Post back if you get stuck again.

MH


"Stephen" wrote in message
...


"Stephen" wrote:

Hi folks,

I'm trying to create a little solution that will allow a user to open
the
.xls and be prompted to input date values which would then be used to
query
the remote SQL DB and return the results to sheet 1 of the .xls. Once
returned I'm going to have to perform some calculations in seperate
subs()
that will fill a range based on an IF statement and calculate some
more
figures based on the SUM of the IF range.

Right now I have a connection established to the DB but I'm having
trouble
passing my parameters through the SQL query, and I haven't even
starting
to
think about how I'm going to return these results.

Any help is greatly appreciated as always... here is what I have so
far...

' Define Input Date Parameters

Dim dtStartDate As Date
Dim dtEndDate As Date

' Ask for Data Range Input
dtStartDate = InputBox("Enter a starting date for the report range.",
"Beginning Date Range", "XX/XX/XXXX")
dtEndDate = InputBox("Enter End Date.", "Ending an ending date for the
report range.", "XX/XX/XXXX")

' Create the connection
Dim dbConn As Object
Dim Sql As String
Set dbConn = CreateObject("ADODB.Connection")
dbConn.Open "Driver={SQL Server}; Server=MY_SQL_SERVER;
Database=mydatabase;Uid=myuser; Pwd=mypassword;"
Sql = "SELECT order_date,order_no,completed FROM oe_hdr WHERE
order_date
=
dtStartDate AND order_date <= dtEndDate"
dbConn.Execute (Sql)
dbConn.Close
Set dbConn = Nothing

I get an error on the dbConn.Excute "invalid column name 'dtStartDate'

Thanks in advance!!!

Hurdle No. 1 completed. I figured out my syntax error in my SQL
statement.
It was actualy two problems in one. First I needed to concat my
statement
string with my variables "where oe_hdr.order_date = " & dtStartDate &
"
...
Then I ran into the problem of datatype mismatchs which I was albe to
fix
by
changes my DateTime variables into strings.

Now I have to figure out how to create the recordset and pass it back
to
my
sheet 1.

Any help is always appreciated.

Thanks!!!