Microsoft Excel - Passing date parameter vba

Hi All,


I need some help to passing the date parameter in Sql below query the date parameter which iam pasing is not working please see in the defined variable what i am doing wrong and not getting data on excel sheet?



Sub Tester(dAsOfDate As Date, dsEndDate As Date)
    Dim wSheet As Worksheet
    Dim wProd As Worksheet
    Dim dStartdate As Date, indate As Date
    Dim dEnddate As Date
    Dim sql As String
    Dim sql1 As String
    Dim sPotRangeUL As String
  
   dStartdate = CDate(dAsOfDate)
   dEnddate = CDate(dsEndDate)
    
  sPotRangeUL= "A2"

    '*******Get input data
Dim Str_dStartdate As String
Dim Str_dEnddate As String
Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))

Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))

'      '------------------------------------------Query-----------------------------------'
  sql = "  SELECT     DATEADD(mm, DATEDIFF(m,1,v2.START_DATETIME),0) as v2, " & _
    "        ISNULL(SUM(v1.Value_1),0)AS Value_1 " & _
" FROM       (select DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0) as START_DATETIME, " & _
 "         ISNULL(SUM(v1.Value_1),0) AS Value_1 " & _
 "          from Table  v1 " & _
  "          where  v1.ITEM_TYPE='COMP " & _
  "          AND  v1.ITEM_NAME IN('Test') " & _
  "                    AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) >= " & Str_dStartdate & " " & _
  "          AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) <= " & Str_dEnddate & " " & _
  "          group by DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0)) v2 left outer join " & _
  "         CUST_TOTALS_DAY_BHIT_BADHRA_ENIPAK v1 on " & _
  "           CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(mm, DATEDIFF(m,1,v1.START_DATETIME),0),101)) " & _
   "          <= CONVERT(DATETIME,CONVERT(VARCHAR,v2.START_DATETIME,101)) " & _
" WHERE          v1.ITEM_TYPE='COMP' " & _
"          AND  v1.ITEM_NAME IN('Test')   " & _
"          AND  CONVERT(DATETIME,CONVERT(VARCHAR,v1.START_DATETIME,101)) <= " & Str_dEnddate & " " & _
" group by    v2.START_DATETIME "

Call dataGetter.GetOperFieldRead(dStartdate, dEnddate, sql, 43, sPotRangeUL, 0, 100)  ''' this will go to record sets to fill the data in the sheet
end sub 



Thanks

Make sure that your input date string go to sql in proper format, it accept in yyyy/mm/dd format. put a breakpoint and check,second run the same query to your db and see if it returns any data.

hope it helps
Regards
D

It is going in that format 201212     while checking with break point,  How can i change the below to the format   "yyyy/mm/dd"  ?

Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))

Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))

Thanks
farrukh

keep variables in string and save
like year=2012/
month=02/
date=17
and add 2012/02/17 , it will work
regrds
D

Replace
With

This would give you the format as same you are passing the value and so value would compared in same format

Check this for your reference


OUPTUT : 201202

sandeep,


Thank you now the results in the excel sheet is coming but problem is i am providing the range 20009
To 201212 

Checking from break point and paste the query on sql server the results are coming since start to end of the date the date parameter is not restricting the query to run between
 20009 To 201212  it is fetching all the records of that query any idea ?


Thank you
Farrukh

Thank you all for your support passing below to query solve the issue



Str_dStartdate = Str(Month(dStartdate)) & "/1/" & Str(Year(dStartdate))
Str_dEnddate = Str(Month(dEnddate)) & "/1/" & Str(Year(dEnddate))



Thanks
farrukh

To solve this, you can simply

REPLACE
WITH

Sandeep ,

Great !

It also worked as you modified the query and passing parameters :-)

Thank you

Farrukh

You are welcome.