ADO SQL Query Ignoring YEAR in the Date

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).

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. Please help me crack this challenge.

Thanks in advance.

Cheers,
Ravi.

Re: ADO SQL Query Ignoring YEAR in the Date

Thanks Ashu for responding.

I tried earlier and also tried by copying text from your response (to make sure it is exact). Did not fetch the expected result.

Date format in .csv file as requested (in bold):
BUY,Gas,C,Forward Transactions,TRD ,NBP97,FIRM,BP Gas Marketing Limited,AUN666,01/01/2015,BUY,TRUE,,1550000,918685,771125,-147273.4961,0,-147273.4961

Cheers,
Ravi.

Re: ADO SQL Query Ignoring YEAR in the Date

Hi Ashu,

It is ofcourse dd/mm/yyyy - I should have sent you a couple of lines:

BUY,Gas,C,Forward Transactions,TRD ,NBP97,FIRM,SSE Energy Supply Limited,AOM195,01/10/2015,BUY,TRUE,,775000,526302.5,378316.25,-146179.6268,0,-146179.6268,
BUY,Gas,C,Forward Transactions,TRD ,NBP97,FIRM,SSE Energy Supply Limited,AOM195,01/11/2015,BUY,TRUE,,750000,509325,390112.5,-117633.0433,0,-117633.0433,
BUY,Gas,C,Forward Transactions,TRD ,NBP97,FIRM,SSE Energy Supply Limited,AOM195,01/12/2015,BUY,TRUE,,775000,526302.5,419523,-105234.0578,0,-105234.0578,

I tried anyways and it is still fetching the wrong value of 21,150,000

If I may say so, the problem is that, the calculation is being made correctly if we consider the DATE-MONTH. The year is being ignored. e.g when I execute the query, the calculation is a sum of Jan, Feb and Mar for all the years available in the database instead of Just for 2015.

Thanks again for sticking with me on this issue.

Cheers,
Ravi.

Re: ADO SQL Query Ignoring YEAR in the Date

Hi Ashu,

I got the same doubt and check the data.

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.

Thank you very much for you time. I'll upload the file and Macro for you once I get back home.

Cheers,
Ravi.