Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Lightbulb Unanswered: database query with date range filter question

    Hi guys. Im trying to figure out how to add a date range filter to my database query. I want to be able to enter a date into ("E,6") and have VBA pull data >,= to that date range according to my "OPENED_DATE" dates. My code is pasted below:

    'Create header names

    wkbOne.Worksheets("Sheet1").Cells(1, 1).Value = "PART_NUMBER"
    wkbOne.Worksheets("Sheet1").Cells(1, 2).Value = "SFC"
    wkbOne.Worksheets("Sheet1").Cells(1, 3).Value = "OPERATION"
    wkbOne.Worksheets("Sheet1").Cells(1, 4).Value = "NC_CODE"
    wkbOne.Worksheets("Sheet1").Cells(1, 5).Value = "OPENED_DATE"
    wkbOne.Worksheets("Sheet1").Cells(1, 6).Value = "NC_CODE_DESCRIPTION"
    wkbOne.Worksheets("Sheet1").Cells(1, 7).Value = "RESOURCE"
    wkbOne.Worksheets("Sheet1").Cells(1, 8).Value = "PGM"
    wkbOne.Worksheets("Sheet1").Cells(1, 9).Value = "COMMENTS"

    wkbOne.Worksheets(1).Rows(1).AutoFilter

    'Get Cost Center Info for each part number listed
    y = 1
    x = 1
    row_cnt = 2
    While (Trim(AppWrkBk.Worksheets("Sheet1").Cells(y, x).Value) <> "")
    ITEM_VAL = Trim(AppWrkBk.Worksheets("Sheet1").Cells(y, x).Value)

    SQLStr = "SELECT ITEM, SFC, OPERATION, NC_CODE, OPENED_DATE_TIME, NC_CODE_DESCRIPTION, RESOURCE, PGM, COMMENTS FROM IDSOPS..NC_RPR WHERE ITEM = '" & ITEM_VAL & "' ORDER BY ITEM, OPENED_DATE_TIME"

    oRs.Open SQLStr, oCn
    If Err.Number <> 0 Then
    Msg = "Error # " & str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    MsgBox "Cannot execute statement " & SQLStr & " Exiting execution."
    End
    End If
    While (Not oRs.EOF)

    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 1).Value = oRs.Fields(0).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 2).Value = oRs.Fields(1).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 3).Value = oRs.Fields(2).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 4).Value = oRs.Fields(3).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 5).Value = oRs.Fields(4).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 6).Value = oRs.Fields(5).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 7).Value = oRs.Fields(6).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 8).Value = oRs.Fields(7).Value
    wkbOne.Worksheets("Sheet1").Cells(row_cnt, 9).Value = oRs.Fields(8).Value
    oRs.MoveNext
    row_cnt = row_cnt + 1
    Wend
    oRs.Close
    y = y + 1
    Wend
    oCn.Close

  2. #2
    Join Date
    Jul 2012
    Posts
    4
    Is it coded like this?

    SQLStr = "WHERE DT <= Application.ActiveWorkbook.Worksheets.("E,6").Valu e & "" _

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •