Creating SQL Strings with variables in Excel VBA

Question

Hello

I know this must be quite a simple fix (well... hopefully!), but I would really appreciate if someone could have a read through my SQL string and let me know what's causing my Syntax error. I believe it will be something to do with the variables:

    Dim cnn1 As ADODB.Connection

    Dim StartDt As Date

    Dim EndDt As Date

    Dim GroupCode As String

    Dim TransTypeCode1 As String

    Dim TransTypeCode2 As String

    Dim strSQL As String

    GroupCode = "SMT"

    TransTypeCode1 = "WPD"

    TransTypeCode2 = "WPC"

    StartDt = Sheet2.Range("A1")

    EndDt = Sheet2.Range("A2")

    strSQL = "SELECT FINANCIAL_TRANSACTION.DEBT_ID, FINANCIAL_TRANSACTION.TRANSACTION_ID, FINANCIAL_TRANSACTION.TRANSACTION_AMOUNT, FINANCIAL_TRANSACTION.TRANSACTION_DATE, DEBT.CLIENT_ID, CLIENT_LOCATION.CLIENT_GROUP_ID" & _

             "FROM FINANCIAL_TRANSACTION LEFT JOIN TRAN_CAT_FOR_TRAN_TYPE ON FINANCIAL_TRANSACTION.TRANSACTION_TYPE_CODE=TRAN_CAT_FOR_TRAN_TYPE.TRANSACTION_TYPE_CODE" & _

             "JOIN DEBT ON FINANCIAL_TRANSACTION.DEBT_ID=DEBT.DEBT_ID" & _

             "JOIN CLIENT_LOCATION ON DEBT.CLIENT_ID=CLIENT_LOCATION.CLIENT_ID" & _

             "WHERE FINANCIAL_TRANSACTION.TRANSACTION_DATE BETWEEN #" & StartDate & "# AND #" & EndDate & "#" & _

             "AND TRAN_CAT_FOR_TRAN_TYPE.TRANSACTION_CAT_GROUP_CODE = " & "'" & GroupCode & "'"

The basic SQL code does work outside of the environment, and my script does work with other SQL strings, so I know that the error must be within the VBA code above.

Thank you

Answer 1

Please show us a sample of the strSQL variable just prior to getting the error message and we'll go from there.

Answer 2

One thing that I notice is that you have:

Dim StartDt as Date

Dim EndDt as Date

But in the code you are using StartDate and EndDate.

Also, SQL dates require a fairly specific date format in text form, and you might want to try using a format statement such as:

...BETWEEN #" & Format(StartDate, "YYYYMMDD") & "#...

Hope this helps,

Eric

Hi Eric - thank you for your help.

I did notice the silly date/dt differences and have recified these. I have also tried formatting as "YYYYMMDD" and "dd/mm/yyyy" which gives the below error message:

[Oracle][ODBC][Rdb]%SQL-F-DATCONERR, Data conversion error for string '01/08/2014'

-COSI-F-AMBDATTIM, ambiguous date-time

I have also tried CAST( AS DATE) on the Transaction_date variable with no luck...

Natalie

Answer 3

One thing that I notice is that you have:

Dim StartDt as Date

Dim EndDt as Date

But in the code you are using StartDate and EndDate.

Also, SQL dates require a fairly specific date format in text form, and you might want to try using a format statement such as:

...BETWEEN #" & Format(StartDate, "YYYYMMDD") & "#...

Hope this helps,

Eric