View Single Post
  #4 (permalink)  
Old August 6th, 2004, 03:34 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

This ran fine for me, with your QueryDef settings commented out. Use Debug.Print strSQL to see where a glitch might be in your SQL string. Probably missing a space or something somewhere.

Private Sub SearchButton_Click()

    Dim strSQL As String, strOrder As String, strWhere As String

    Dim db As DAO.Database
    Dim qryDef As DAO.QueryDef
    Set db = CurrentDb()

    strSQL = "SELECT tbl_Vendor.F_Name, tbl_Vendor.L_Name, tbl_Vendor.Company, tbl_Vendor.ST " & _
             "FROM tbl_Vendor"

    strWhere = ""

    strOrder = "ORDER BY tbl_Vendor.L_name;"

    If Not IsNull(Me.F_Name) Then
        strWhere = strWhere & " (tbl_Vendor.F_name) Like '*" & Me.F_Name & "*' AND"
    End If

    If Not IsNull(Me.L_Name) Then
        strWhere = strWhere & " (tbl_Vendor.L_Name) Like '*" & Me.L_Name & "*' AND"
    End If

    If Not IsNull(Me.Company) Then
        strWhere = strWhere & " (tbl_Vendor.Company) Like '*" & Me.Company & "*' AND"
    End If

    If Not IsNull(Me.ST) Then
        strWhere = strWhere & " (tbl_Vendor.ST) Like '*" & Me.ST & "*' AND"
    End If

    strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

' Set qryDef = db.QueryDefs("qry_SearchV")
'
' Debug.Print strSQL & " " & strWhere & "" & strOrder
' qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

     DoCmd.OpenForm "Form2", acNormal, , strWhere
' DoCmd.OpenQuery "qry_SearchV", acNormal

' qryDef.Close


Exit_SearchButton_Click:
        Exit Sub

End Sub

Bob



Reply With Quote