Method Community

 

API Pull Data into Excel based off 2 Date Fields from Worksheet

Last post 04-12-2012 8:50 AM by Method_David. 1 replies.
Page 1 of 1 (2 items)
Sort Posts: Previous Next
  • 04-11-2012 2:12 PM

    API Pull Data into Excel based off 2 Date Fields from Worksheet

    OK, I have a question about using 2 cells as the parameters for a SQL Statement.

    This code is not working because it is not recognizing STARTDATE and ENDDATE as parameters:

     

        Dim STARTDATE As Date
        Dim ENDDATE As Date
        STARTDATE = Range("B1").Value
        ENDDATE = Range("B2").Value
           
        'Write my SQL statement to get any FSR information
        Dim sSelectFields As String
        Dim sSelectFrom As String
        Dim sSelectWhere As String
        Dim sSelectGroupBy As String
        Dim sSelectHaving As String
        Dim sSelectOrderBy As String
        sSelectFields = "ServiceDate,CallReportNo,FieldServiceEngineerAssigned,CallTypes,Facility,PhysicianName,FailureType,RepairCodes,ReviewedByDate"
        sSelectFrom = "FieldServiceReport"
        sSelectWhere = "ServiceDate <> '' AND ServiceDate >= STARTDATE AND ServiceDate <= ENDDATE AND NOT(CallReportNo LIKE '%TEST%') AND NOT(CallReportNo LIKE '%CREU%')"
        sSelectGroupBy = ""
        sSelectHaving = ""
        sSelectOrderBy = "ServiceDate"

     

    Thanks in advance.

    -Josh

  • 04-12-2012 8:50 AM In reply to

    Re: API Pull Data into Excel based off 2 Date Fields from Worksheet

    Answer

     Hi CDS_Josh,

    How are you formatting the dates? Try formatting them as 'yyyy/MMM/dd' or 'yyyy/MMM/dd hh:mm:ss tt'

    So for example 'April 12, 2012' would be formatted as '2012/Apr/12'

    And 'April 12, 2012, 10am' would be formatted as '2012/Apr/12 10:00:00 AM'

    Dave

Page 1 of 1 (2 items)