search button (1 Viewer)

[luzz]

Hi all, I have 5 textbox that link to a search button on my form, and the code works fine when i fill up all 5 textbox with PO number and click search it will show data that belong to the 5 PO number that i keyed in.
However, i do face issue when i did not fill up all the 5 textbox. For example; if i just fill up 3 textbox with PO number and leave the remaining two blank and click search, the listbox will not show just the 3 PO number that i keyed in. How can i fix that?

Below is my code:

Private Sub txtSearch_Click()
'declare variable
Dim sql As String
    'Determine and return the number of records in UnmatchFabricPOqry
    If DCount("*", "UnmatchNewFabricPOQry", "PO like '*" & Me.txtKeyword & "*'") & _
             ("PO like '*" & Me.txtKeyword1 & "*'") & _
             ("PO like '*" & Me.txtkeyword2 & "*'") & _
             ("PO like '*" & Me.txtkeyword3 & "*'") & _
             ("PO like '*" & Me.txtkeyword4 & "*'") = 0 Then
    'Error message will be display if PO number is not found
    MsgBox "No record found,check your PO again!"
    Else
    sql = "SELECT UnmatchNewFabricPOQry.PO, UnmatchNewFabricPOQry.[Style NO], UnmatchNewFabricPOQry.[GL Lot], " & _
        "UnmatchNewFabricPOQry.Date, UnmatchNewFabricPOQry.Description2, UnmatchNewFabricPOQry.[Fabric Cuttable Width], " & _
        "UnmatchNewFabricPOQry.Color, UnmatchNewFabricPOQry.[Our Qty], UnmatchNewFabricPOQry.[Supplier Qty]," & _
        "UnmatchNewFabricPOQry.GSMBeforeWash, UnmatchNewFabricPOQry.[GMS Per SqYD], UnmatchNewFabricPOQry.Remark, " & _
        "UnmatchNewFabricPOQry.[Fabric Weight], UnmatchNewFabricPOQry.[Unit Price], " & _
        "UnmatchNewFabricPOQry.[Garment Delivery Date], UnmatchNewFabricPOQry.Line, UnmatchNewFabricPOQry.ShipName, " & _
        "UnmatchNewFabricPOQry.POStatus, UnmatchNewFabricPOQry.[PO Amend No], UnmatchNewFabricPOQry.GSMAfterWash " & _
        "FROM UnmatchNewFabricPOQry WHERE PO LIKE '*" & Me.txtKeyword & "*' or PO like '*" & Me.txtKeyword1 & "*' or PO like '*" & Me.txtkeyword2 & "*' or PO like '*" & Me.txtkeyword3 & "*' or PO like '*" & Me.txtkeyword4 & "*';"
    Me.Lst_PO.RowSource = sql
    Me.Lst_PO.Requery
    End If
End Sub

 

[arnelgp]

Here Ms.Luzz try this.

Private Sub txtSearch_Click()
'declare variable
Dim sql As String
Dim criteria As String

    'build criteria first
    'for 5 textboxes
    If Nz(Me.txtKeyword1, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword1
    End If
    If Nz(Me.txtKeyword2, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword2
    End If
    If Nz(Me.txtKeyword3, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword3
    End If
    If Nz(Me.txtKeyword4, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword4
    End If
    If Nz(Me.txtKeyword5, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword5
    End If
    'check if we have a criteria
    If Len(criteria) <> 0 Then
        'selected po only
        criteria = "[PO] In (" & Mid(criteria, 3) & ")"
    Else
        'all po
        criteria = "(1=1)"
    End If
    'Determine and return the number of records in UnmatchFabricPOqry
    If DCount("*", "UnmatchNewFabricPOQry", criteria) = 0 Then
        'Error message will be display if PO number is not found
        MsgBox "No record found,check your PO again!"
    Else
        'If PO number is found, it will retrive the data below from UnmatchFabricPOqry
        sql = "SELECT PO, [Style NO], [GL Lot], [Date], Description2, [Fabric Cuttable Width], " & _
            "Color, [Our Qty], [Supplier Qty], " & _
            "GSMBeforeWash, [GSM Per SqYD], [Remark], " & _
            "[Fabric Weight], [Unit Price], " & _
            "[Garment Delivery Date], [Line], ShipName, " & _
            "POStatus, [PO Amend No], GSMAfterWash " & _
            "From UnmatchNewFabricPOQry Where " & criteria & ";"
        
        Me.List_PO.RowSource = sql
        Me.List_PO.Requery
    End If
End Sub

 

[luzz]

Here Ms.Luzz try this.

Private Sub txtSearch_Click()
'declare variable
Dim sql As String
Dim criteria As String

    'build criteria first
    'for 5 textboxes
    If Nz(Me.txtKeyword1, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword1
    End If
    If Nz(Me.txtKeyword2, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword2
    End If
    If Nz(Me.txtKeyword3, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword3
    End If
    If Nz(Me.txtKeyword4, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword4
    End If
    If Nz(Me.txtKeyword5, "") <> "" Then
        criteria = criteria & ", " & Me.txtKeyword5
    End If
    'check if we have a criteria
    If Len(criteria) <> 0 Then
        'selected po only
        criteria = "[PO] In (" & Mid(criteria, 3) & ")"
    Else
        'all po
        criteria = "(1=1)"
    End If
    'Determine and return the number of records in UnmatchFabricPOqry
    If DCount("*", "UnmatchNewFabricPOQry", criteria) = 0 Then
        'Error message will be display if PO number is not found
        MsgBox "No record found,check your PO again!"
    Else
        'If PO number is found, it will retrive the data below from UnmatchFabricPOqry
        sql = "SELECT PO, [Style NO], [GL Lot], [Date], Description2, [Fabric Cuttable Width], " & _
            "Color, [Our Qty], [Supplier Qty], " & _
            "GSMBeforeWash, [GSM Per SqYD], [Remark], " & _
            "[Fabric Weight], [Unit Price], " & _
            "[Garment Delivery Date], [Line], ShipName, " & _
            "POStatus, [PO Amend No], GSMAfterWash " & _
            "From UnmatchNewFabricPOQry Where " & criteria & ";"
        
        Me.List_PO.RowSource = sql
        Me.List_PO.Requery
    End If
End Sub

Thank you so much!

 

[el.alcalde]

You can use the next Function to extend your SQL and call it every time you exit a search box

Public Function ExtendSQL(pstrSQL As String, pstrWhere As String, Optional pstrOrderBy As String) As String
'-----------------------------------------------------------------------
' Funktion:       SQL-Statement um where-Claus und Orderby erweitern
' Erstellt:         CM. 7.02.2008  .
' - pstrSQL:      SQL-Statement
' - pstrWhere:    Where-Clause, ohne "where". Wird an bereits vorhandene Where-Clause angefügt
' - pstrOrderBy:  OrderBy-Claus, ohne "order by". Wird an bestehendes OrderBy angefügt
' - return:       Erweitertes SQL-Statement
'-----------------------------------------------------------------------
Dim strOrderBy As String
Dim strSql As String
Dim intOrderBy As Integer

On Error GoTo ErrHandle
        
    strSql = Trim(pstrSQL)
    If Right(strSql, 1) = ";" Then strSql = Mid(strSql, 1, Len(strSql) - 1)
    
' Order by
    intOrderBy = InStr(1, strSql, "order by", vbTextCompare)
    If intOrderBy > 0 Then
        ' order by clause schon da -> anfügen
        strOrderBy = Mid(strSql, intOrderBy)
        If pstrOrderBy <> "" Then strOrderBy = strOrderBy & "," & pstrOrderBy
        strSql = Left(pstrSQL, intOrderBy - 1)
    Else
        If pstrOrderBy <> "" Then strOrderBy = " order by " & pstrOrderBy
    End If

' where
    If InStr(1, strSql, "where ", vbTextCompare) > 0 Then
        ' where clause schon da -> anfügen
        strSql = strSql & " and (" & pstrWhere & ")"
    Else
        strSql = strSql & " where " & pstrWhere
    End If
    
    If strOrderBy <> "" Then strSql = strSql & " " & strOrderBy
    
    ExtendSQL = strSql


Exit Function

ErrHandle:
  Select Case Err
      Case Else   'Sonstige Fehler
          ErrHandle Err.Description, "ExtendSQL"
          Exit Function
  End Select
  Resume
End Function