Using Excel cell reference as a parameter in a SQL statement

I have created a SQL query procedure called from Excel by an External SQL connection:

There is a Where clause to limit the data by a date field between "StartDate" and "EndDate".

I want to be able to reference a cell value in Excel for the Start and End date.

What is the syntax for the SQL code?

Thanks

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
You will have to add a parameter to the query and pass it when the SP is called.
0
GPSPOWAuthor Commented:
Can you store a date value in Excel VBa and pass that to SQL statement?
0
lluddenCommented:
Yes.  If you are calling the SQL from vba, you can pass it arguments.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SixSigmaGuyCommented:
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
0
GPSPOWAuthor Commented:
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
0
winheimCommented:
the simplest way in my opinion is to write the query  and replace the Parameters with question marks like this WHERE Date1 >= ? AND Date1 <= ?

After you quit the query Editor Excel is asking you about the missing parameters and you can select the cells you would like to refer to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
It took a while but I got it to work.

Thanks

Glen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.