"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!!!