ADO SQL Query is Ignoring YEAR in the Date

[ravikiran]

Hi Excel Gurus,

I have this weird problem that is bugging me since yesterday.

I am trying to Automate a complex dashboard that queries data from various data input files (in CSV). I am using ADO connection to fetch the calculation queries such as below.

The issue I have is when I run this query, it ignores the YEAR specific in the Date. For e.g. the following query should return the sumvalue of 16,600,000 for (Jan-15 + Feb-15 + Mar-15). Though it returns 21,150,000 (Jan-15 + Feb-15 + Mar-15, Jan-16 + Feb-16 + Mar-16, Jan-17 + Feb-17 + Mar-17).

VB:

strSQL = "SELECT One.COMMODITY, One.TRANSACTION, One.ADJBS, Sum(One.[" & strCalc & "]) as SumValue " & _ "FROM DerData.csv AS One LEFT JOIN DelCustomers.csv as Two ON One.COUNTERPARTY = Two.COUNTERPARTY " & _
"WHERE (((One.DELIVERY_MONTH)>=" & sDate & " AND (One.DELIVERY_MONTH)<" & eDate & ") " & _
"AND ((One.FIRMNESS)<>'FLEXIBLE') AND ((One.COUNTERPARTY) Not In (SELECT COUNTERPARTY FROM DelCustomers.csv))) " & _
"GROUP BY One.COMMODITY, One.TRANSACTION, One.ADJBS " & _
"HAVING (((One.COMMODITY)='" & strCommodity & "') AND ((One.TRANSACTION)='" & strTransaction & "') AND ((One.ADJBS)='" & strAjdBS & "'))"

objRecordset.Open strSQL, objConnection, adopenstatic, adlockoptimistic, adcmdtext
FetchCalc2 = objRecordset.Fields.Item("SumValue")

SQL as Debug.Print below:

SELECT One.COMMODITY, One.TRANSACTION,
One.ADJBS,
Sum(One.[VOLUME]) As SumValue
FROM DerData.csv As One
LEFT JOIN DelCustomers.csv As Two ON One.COUNTERPARTY = Two.COUNTERPARTY
WHERE (((One.DELIVERY_MONTH)>=#2015-01-01 00:00:00#
And (One.DELIVERY_MONTH)<#2015-04-01 00:00:00#)
And ((One.FIRMNESS)<> 'FLEXIBLE')
And ((One.COUNTERPARTY) Not In
(SELECT COUNTERPARTY
FROM DelCustomers.csv)))
GROUP BY One.COMMODITY,
One.TRANSACTION,
One.ADJBS
HAVING (((One.COMMODITY)= 'Gas')
And ((One.TRANSACTION)= 'Forward Transactions')
And ((One.ADJBS)= 'BUY'))

I tried passing dates in various formats #dd/mm/yyyy#, #dd/mm/yy#, 'dd/mm/yyyy' etc with no luck. Please help me crack this challenge.

Thanks in advance.

Cheers,
Ravi.

 

[ravikiran]

Hi Guys,

I got this working now. Seems like the problem is with the Extract. When I extract the CSV file with Date formatted as TEXT, ADO engine is able to identify the date correctly and calculate the correct value.

Thanks for you time.

Cheers,
Ravi.