Equal or less then, does not work when passing a date to SQL via vba for Teradata

Hi all,

I'm able to run SQL in Teradata SQL Assistant and getting results.
When I connect via excel and run the query from there, the rest of the query works, but the equal or less than date not.

This is the vba code for hte SQL query:

thisSql = "SELECT  TOP 1 BALANCE  " & _
"FROM tbl_CUSTOMER_DB  " & _
"WHERE ((BRANCH_NO)= " & branch & ") AND ((ACCOUNT_NO)= " & Acc & ") AND ((MVT_DATE) <= " & ToDate & ") Order By MVT_DATE desc "

It totaly ignores the "<=" as the date in the "ToDate" is the 31/08/2016 and in the outcome of the query dates in end of September are in the query results.
(dates are entered in Teradata SQL as text: 20160831)

Any chances this can be fixed?

Greetings

Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata

hi Cytop,

Looks like I got it wrong. When I enter a date directly in Teradata, it needs to be like this: '2016.08.31'
From the vba sql, it needs to be like this 20160831, but now I wonder if it is numeric or just general format (in the excel format of the cell it is as general)
When I use your code, I get the error: Syntax error: expected something between the word '20160831' and ')'

There is something I just don't see.

Greetings.

Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata

hi, I used the above, but still the same. It errors like in the previous one and no chance to get this working.
The strange thing is, that when I pass a Between in the dates, it works fine

"WHERE ((TRANSACTION_DATE) Between " & FromDate & " And " & ToDate & ")AND ((BRANCH_NO)= " & branch & ") AND ((ACCOUNT_NO)= " & Acc & ") Order By TRANSACTION_DATE "

Wonder why the Between is working and not the <=

Greetings.

Re: Equal or less then, does not work when passing a date to SQL via vba for Teradata

Hi,
I actually had a more close look ot the column properties. It turns it is set as date (the MVT_DATE) and DECIMAL(9,0) the (Transaction_Date)
maybe there is the whole thing that when I pass the 20160831 in the (Transaction_Date), it works, but the date format is causing the (MVT_DATE) not to be seen.

Maybe this can help to figure out what is going on.

Greetings.