Using Excel cell reference as a parameter in a SQL statement

You will have to add a parameter to the query and pass it when the SP is called.

Can you store a date value in Excel VBa and pass that to SQL statement?

Yes.  If you are calling the SQL from vba, you can pass it arguments.

I think a better way to do it (it's easier to debug) is to write some VBA code that pulls the values from the worksheet cells and then you concatenate them to your SQL string.  I'm not a SQL expert, so excuse me if my SQL syntax is wrong, but it should give you the idea.

Say you have the start date stored in cell A1 and the end date stored in cell A2.

Sub SendSQL()
     Dim stStartDate as String  ' or "as Date" if you need to convert the date formats
     Dim stEndDate as String
     Dim stSQL as String

     stStartDate = Me.Cells(1, 1)
     stEndDate = Me.Cells(1, 2)

     stSQL = "Select * Where StartDate > " & stStartDate & " AND EndDate < " & stEndDate

     '
     ' Send SQL string
     '
End Sub

I read a thread from DataPig explaining how you can use MS Query in conjunction with MS Access:

Here is my MS Query Code:

SELECT VW_NHSN_PATIENTS.VisitID, VW_NHSN_PATIENTS.AccountNumber, VW_NHSN_PATIENTS.BirthDateTime, VW_NHSN_PATIENTS.UnitNumber, VW_NHSN_PATIENTS.SurgeonID, VW_NHSN_PATIENTS.SurgeonName, VW_NHSN_PATIENTS.Description, VW_NHSN_PATIENTS.DateTime
FROM `C:\Documents and Settings\gpowers\My Documents\NHSN.accdb`.VW_NHSN_PATIENTS VW_NHSN_PATIENTS
WHERE (VW_NHSN_PATIENTS.SurgeonID='CHANI') AND (VW_NHSN_PATIENTS.DateTime>={ts '2013-07-01 07:00:00'} And VW_NHSN_PATIENTS.DateTime<={ts '2013-08-16 12:30:00'})


How can pass the SurgeonID and DateTime parameters from a cell reference with the spreadsheet?

Thanks

Glen

It took a while but I got it to work.

Thanks

Glen