Question about setting a Dim for, then inserting it into an SQL WHERE string

  • I am stuck trying to automate a code for work. Due to the sensitivity of the sql connection, I cant post a bunch of the code, and some of it will be xxxxx'd out for security reasons. That being said I have some code written by someone who actually knows what they are doing, and I am just tweaking it and adding rudimentary actions like clearing/copying cells before and after the meat of the program is ran. One section is a WHERE statement of an external connection pulling from a corporate database. One of the AND statements in it targets date data. I was able to figure out how to make that an AND/OR statement so I could make it target three date ranges. Previously (and me currently) the way the query was made was to manually edit the macro for the date, then run it. I want to automate that portion.

    So where I am stuck is assigning a Dim (I was thinking something simple like date1, date2, ... ) from another worksheet. In this case the first one would be Dates!B4 . In that page I put in a date for the Saturday of a week I want to project data for, and six other dates are calculated which encompass three ranges. One from earlier this year, and two from last year. I do not know the proper syntax that would be compatible with the SQL portion - it apparently wants the date in a dd-mmm-yyyy format, which is what my output cells in the worksheet spit out.

    Now for the sql part. Here is a small portion of the lookup code that I will need to figure out how to reference the date Dim's. The dates in there are arbitrary for now.

    1. 'This is the end of a WHERE statement
    2. "AND (APPLICATION_DATA.xxxx_DATE BETWEEN '2-jan-2020' AND '2-jan-2020' OR APPLICATION_DATA.xxxx_DATE BETWEEN '13-jan-2020' AND '13-jan-2020' OR APPLICATION_DATA.xxxx_DATE BETWEEN '4-jan-2020' AND '4-jan-2020')", conn

    Any help is appreciated, I am very green with VBA and have never touched anything pulling external data before.

    Thank you in advance

  • See if helps

  • I did end up getting this, forgot to update. Had to do a conversion in the SQL Line.

    Dim "date1" and "date2" as dates, and target a cell. then:

    1. "AND APPLICATION_DATA.MODS_DATE BETWEEN TO_DATE('" & CStr(Date1) & "','MM-DD-YYYY') AND TO_DATE('" & CStr(Date2) & "','MM-DD-YYYY')", conn