VBA to SQL DB with user input and results to sheet

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> >
>> > "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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> >
> >> > "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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >
> > >> >
> > >> > "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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> >
> > > >> >
> > > >> > "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!!!
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>