CODEWITHSUNDEEP
sqlexcelvba
amnoyes97
amnoyes97

Reputation: 23

How do I properly input an sql where clause in VBA?

The where clause in the code below is throwing me an error. I just can't seem to get the syntax right.

    today = Date
    monthBack = today - 30
    strSQL = ""
    strSQL = strSQL + "SELECT *"
    strSQL = strSQL + "FROM PROD.LABOR_ALLOCATION_HIST"
    strSQL = strSQL + "WHERE LaborDate BETWEEN today AND monthBack"

Upvotes: 0

Views: 81

Answers (3)

CDP1802
CDP1802

Reputation: 16392

Alternatively without variables

strSQL = " SELECT * FROM PROD.LABOR_ALLOCATION_HIST" & _
         " WHERE DATEDIFF(day,LaborDate,GetDate()) BETWEEN 0 and 30"

Upvotes: 0

norie
norie

Reputation: 9867

The values of the variables need to be added to the SQL, not their names, and the values need to be in an appropriate format.

The following code will add the dates in yyyy-mm-dd format.

today = Date
monthBack = today - 30
strSQL = ""
strSQL = strSQL & "SELECT *"
strSQL = strSQL & "FROM PROD.LABOR_ALLOCATION_HIST"
strSQL = strSQL & "WHERE LaborDate BETWEEN '" & Format(today, "yyyy-mm-dd") & "' AND  '" & Format(monthBack, "yyyy-mm-dd") & "'"

Upvotes: 2

dougp
dougp

Reputation: 3089

today = Date
    monthBack = today - 30
    strSQL = ""
    strSQL = strSQL & "SELECT *" & vbCrLf
    strSQL = strSQL & "FROM PROD.LABOR_ALLOCATION_HIST" & vbCrLf
    strSQL = strSQL & "WHERE LaborDate BETWEEN " & today & " AND " & monthBack

...but you'll need to convert today and monthBack to strings that contain something SQL will read as a valid date.

Upvotes: 1

Related Questions