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"

Answer

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") & "'"