• Home
 · 
• Miscellaneous
 · 
• Filter data in data grid...

Filter data in data grid...

Filter data in data grid...

Rate Filter data in data grid...

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Dim m_dlgFromDate As frmCalendar
Dim m_dlgToDate As frmCalendar

Public g_strFilter As String
Public g_strDateMin As String
Public g_strDateMax As String

Dim WithEvents m_adoParts As Recordset

'Dim m_adoparts As Recordset
Dim m_dbConnection5 As Connection

Public Sub SetDBOject(dbObject As Recordset, dbConnection As Connection)

    If Not IsNull(dbObject) Then Set m_adoParts = dbObject
    If Not IsNull(dbConnection) Then Set m_dbConnection5 = dbConnection

End Sub

Private Sub cboDate_Change()
    cboDate_Click
End Sub

Private Sub cboDate_Click()
    If cboDate.ListIndex = 4 Then
        frmDate.Enabled = True
        lblFrom.Enabled = True
        lblTo.Enabled = True
        txtFrom.Enabled = True
        txtTo.Enabled = True
    Else
        frmDate.Enabled = False
        lblFrom.Enabled = False
        lblTo.Enabled = False
        txtFrom.Enabled = False
        txtTo.Enabled = False
    End If
    SetFilterDate

    txtFrom.Text = g_strDateMin
    txtTo.Text = g_strDateMax
End Sub

Private Sub cmdCancel_Click()
    Me.Hide
End Sub

Private Sub cmdFrom_Click()
    m_dlgFromDate.show vbModal
    If m_dlgFromDate.m_bFlag = True Then
        txtFrom.Text = m_dlgFromDate.m_strDate
    End If
    SetFilterDate
End Sub

Private Sub cmdOK_Click()
    Dim strSQL As String
    Dim strFilter As String


    If cboFilter.ListIndex = 0 Then
         strFilter = ""
    ElseIf cboFilter.ListIndex = 1 Then
'        strFilter = "<> 'Finished Inspection'"
        strFilter = "= 'For Inspection'"
    ElseIf cboFilter.ListIndex = 2 Then
        strFilter = "= 'Finished Inspection'"
    End If

    If g_strDateMin = "" Or g_strDateMax = "" Then
        MsgBox MESSAGE1, vbOKOnly
        Exit Sub
    ElseIf CDate(g_strDateMin) > CDate(g_strDateMax) Then
        MsgBox MESSAGE4, vbOKOnly
        Exit Sub

    End If

    Me.MousePointer = vbHourglass

    m_adoParts.Close

    strSQL = "SELECT Delivery_Date, Item_ID, Item_Name, Actual_Qty," + _
        "Unit_Price, IPMS_Price, Invoice_Number, Supplier_Order_Number, OPI_Order_Number, Lack_XCS, " + _
        " Received_Date, Supplier, Product_ID, Product_Name,  Invoice_Qty," + _
        "Received_by, Total_Amount, OK_Qty, NG_Qty, Inspected_by, Accepted_by," + _
        "Accepted_Date, Remarks, Status, PR_code, ID from PartsReceiving where Delivery_Date <= #" + g_strDateMax + "#" + _
    " AND DELIVERY_DATE >= #" + g_strDateMin + "# AND STATUS " + strFilter + "order by DELIVERY_DATE"

    m_adoParts.Open strSQL, m_dbConnection5, adOpenStatic, adLockOptimistic
    m_adoParts.Requery

    cmdCancel_Click

     Me.MousePointer = vbDefault

End Sub

Private Sub cmdTo_Click()
    m_dlgToDate.show vbModal
    If m_dlgToDate.m_bFlag = True Then
        txtTo.Text = m_dlgToDate.m_strDate
    End If
    SetFilterDate
End Sub

Private Sub Form_Load()
    Set m_dlgFromDate = New frmCalendar
    Set m_dlgToDate = New frmCalendar


    cboDate.AddItem "3 Months", 0
    cboDate.AddItem "6 Months", 1
    cboDate.AddItem "9 Months", 2
    cboDate.AddItem "12 Months", 3
    cboDate.AddItem "Custom...", 4

    cboFilter.AddItem "View All", 0
    cboFilter.AddItem "For Inspection", 1
    cboFilter.AddItem "Finished Inspection", 2


    txtFrom.Text = g_strDateMin
    txtTo.Text = g_strDateMax

End Sub


Private Sub SetFilterDate()
    If cboDate.ListIndex = 0 Then
        g_strDateMin = Format(DateAdd("m", -3, Now), "mm/dd/yyyy")
        g_strDateMax = Format(Now, "mm/dd/yyyy")
    ElseIf cboDate.ListIndex = 1 Then
        g_strDateMin = Format(DateAdd("m", -6, Now), "mm/dd/yyyy")
        g_strDateMax = Format(Now, "mm/dd/yyyy")
    ElseIf cboDate.ListIndex = 2 Then
        g_strDateMin = Format(DateAdd("m", -9, Now), "mm/dd/yyyy")
        g_strDateMax = Format(Now, "mm/dd/yyyy")
    ElseIf cboDate.ListIndex = 3 Then
        g_strDateMin = Format(DateAdd("m", -12, Now), "mm/dd/yyyy")
        g_strDateMax = Format(Now, "mm/dd/yyyy")
    ElseIf cboDate.ListIndex = 4 Then
        g_strDateMin = txtFrom.Text
        g_strDateMax = txtTo.Text
    End If
End Sub