Avatar of GPSPOW
GPSPOWFlag for United States of America asked on

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
Microsoft ExcelMicrosoft SQL Server

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
lludden

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

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

Yes.  If you are calling the SQL from vba, you can pass it arguments.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SixSigmaGuy

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
ASKER
GPSPOW

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
ASKER CERTIFIED SOLUTION
winheim

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
GPSPOW

It took a while but I got it to work.

Thanks

Glen
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.