VBA to SQL DB with user input and results to sheet

[Stephen]

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]

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??

 

[Tim Williams]

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.

 

[Stephen]

Fantastic, that did it!

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.