Results 1 to 8 of 8

Thread: SQL Statement

  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58

    Unanswered: SQL Statement

    I have a form with 4 text boxes and I have a listbox which displays the results.

    txtMake
    txtModel
    txtColor
    txtUnitStatus

    I want to be able to seach these fields for a records that meet the criteria. For example, if I type in Honda for make I want all of the records with the make of honda to be displayed. If I type in Honda and Red I want it to display all of the Red Hondas. I am connecting them using an and statement but I am having problems when I don't type in a seach string for all of the boxes.
    Code:
    "SELECT Inv_ID, StockNbr, Vn_Nbr, UnitStatus, Year, Make, Model, Color 
    FROM tblInventory WHERE make LIKE '" & Nz(Me.txtMake, "*") & "' 
    AND model LIKE '" & Nz(Me.txtModel, "*") & "' AND color LIKE '" 
    & Nz(Me.txtColor, "*") & "'"
    The problem is that it is not pulling up records that I know are there.

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Write your SQL statement dynamically as this example:

    If Trim(txtMake) <> "" Then
    strCriteria = strCriteria & "Make LIKE '" & txtMake & "*'"
    End If

    If Trim(txtModel) <> "" Then
    strCriteria = strCriteria & "AND Model LIKE '" & txtModel & "*'"
    End If

    If Trim(txtColor) <> "" Then
    strCriteria = strCriteria & "AND Color LIKE '" & txtColor & "*'"
    End If

    If strCriteria <> "" Then

    strCriteria= "WHERE " & strCriteria

    strSQL = "SELECT..." & strCriteria
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    I tried the code that you posted and it is not working.
    Code:
    Private Sub cmdSearch_Click()
    Dim strSQLSearch As String
    
    
    If Trim(Me.txtMake) <> "" Then
    strCriteria = strCriteria & "Make LIKE '" & txtMake & "*'"
    End If
    
    If Trim(Me.txtModel) <> "" Then
    strCriteria = strCriteria & "AND Model LIKE '" & txtModel & "*'"
    End If
    
    If Trim(Me.txtColor) <> "" Then
    strCriteria = strCriteria & "AND Color LIKE '" & txtColor & "*'"
    End If
    
    If strCriteria <> "" Then
    
    strCriteria = "WHERE " & strCriteria
    
    strSQSearch = "SELECT Inv_ID, StockNbr, Year, Make, Model, Color, UnitStatus FROM tblInventory" & strCriteria
     
        'Dim strSQLSearch As String
    
        'strSQLSearch = "SELECT Inv_ID, StockNbr, Year, Make, Model, Color, UnitStatus FROM tblInventory WHERE make = '" & Me.txtMake & "' or model = '" & Me.txtModel & "' OR color = '" & Me.txtColor & "'"
        'strSQLSearch = "SELECT Inv_ID, StockNbr, Vn_Nbr, UnitStatus, Year, Make, Model, Color FROM tblInventory WHERE make LIKE '" & Nz(Me.txtMake, "*") & "' AND model LIKE '" & Nz(Me.txtModel, "*") & "' AND color LIKE '" & Nz(Me.txtColor, "*") & "'"
        Me.lstInventory.RowSource = strSQLSearch
        Me.lstInventory.Requery
    
    End If
    
    End Sub

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try it like this:

    dim strSQL as string
    strSQL = "SELECT whatever FROM yourtable WHERE ((1=1) " ' say "thankyou rudy" oops: missing "H"
    if len(nz(me.txtMake, "")>0 then
    strSQL = strSQL & "AND ([Make] Like '" & trim(me.txtMake) & "*') "
    endif
    if len(nz(me.whatever.....etc
    strSQL = ......etc
    endif
    strSQL = strSQL & ");"

    izy
    Last edited by izyrider; 04-30-04 at 12:18.
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    As in the past ... Here once again by popular demand (by proxy) an example of building a filter for a SQL statement ... And judging by the response thereto it's a very overwhelming piece of code (except to Izy) ... So bang you heads on the table once again ...

    Code:
    Function ConstructFilter() As String
        Dim FilterString As String
        Dim HaveFrom As Boolean, HaveThru As Boolean
        
        FilterString = "( "
        If SearchOnGroup.Value <> 0 Then
            Select Case SearchOnGroup.Value
                Case 1  ' Project Title
                    FilterString = FilterString & "[Work Orders II].[Project Title]"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 2  ' Work Order
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 3  ' Brand
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber LIKE '%" & BrandComboBox.Value & "%'"
                Case 4  ' Requestor
                    FilterString = FilterString & "[Work Orders II].OwnerID='" & OwnerComboBox.Value & "'"
                Case 5  ' Customer
                    FilterString = FilterString & "CustomerContacts.ContactID=" & CustomerComboBox.Value
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        If OpenGroup.Value <> 0 Then
            If SearchOnGroup.Value <> 0 Then FilterString = FilterString & " AND ( "
            Select Case OpenGroup.Value
                Case 1  ' Open
                    FilterString = FilterString & "[Work Orders II].Closed=False"
                Case 2  ' Closed
                    FilterString = FilterString & "[Work Orders II].Closed=True"
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        HaveFrom = False
        HaveThru = False
        If FromTxt.Value & "" <> "" Then HaveFrom = True
        If ThruTxt.Value & "" <> "" Then HaveThru = True
        If HaveFrom Or HaveThru Then
            FilterString = FilterString & " AND ( [Work Orders II]."
            If OpenGroup.Value = 2 Then
                FilterString = FilterString & "ClosingDate"
            Else
                FilterString = FilterString & "IssueDate"
            End If
        End If
        If HaveFrom And HaveThru Then
        FilterString = FilterString & " BETWEEN #" & FromTxt.Value & "# AND #" & ThruTxt.Value & "#"
        Else
            If HaveFrom Then FilterString = FilterString & "=#" & FromTxt.Value & "#"
            If HaveThru Then FilterString = FilterString & "=#" & ThruTxt.Value & "#"
        End If
        If HaveFrom Or HaveThru Then FilterString = FilterString & " )"
        
        ConstructFilter = FilterString
    End Function
    - Mike

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Mike,

    i can't tell if you mean my code is "underwhelming" or...



    this site seems to have been optimised for folk using 320x240 screens on their mobile phones, so, after a copy/paste to notepad to make the thing legible, i see where you are coming from. it's a bit heavy for four little text boxes, but it's good for discipline.

    TGIF izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    hi Mike,

    i can't tell if you mean my code is "underwhelming" or...



    this site seems to have been optimised for folk using 320x240 screens on their mobile phones, so, after a copy/paste to notepad to make the thing legible, i see where you are coming from. it's a bit heavy for four little text boxes, but it's good for discipline.

    TGIF izy
    Not in the slightest Izy ... Also I don't write examples tailored to specific requests (for the most part) ... I'll put up something I wrote and leave it up to the poster to extract what they need/want ... Just like my posting for you exampling the NotInList issue with comboboxes ...

    Then sometimes I just post code to show the less experienced among us a way of programming code that should easily be understood ...

    - Mike

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    For those of us who use code to communicate with Word and then avoid it like the plague

    As best I can work out you have 14 combinations of search.

    a
    a b
    a c
    a d
    a b d
    a c d
    b
    b c
    b d
    b c d
    c
    c d
    d
    a b c d

    I would put four unbound text boxes on my form. Let's call then T1, T 2, T 3 and T4 but obviously label them Make, Model, Colour and Unit Status

    I would then make 14 queries and the criteria in the appropriate field or fields would be [Forms]![CarForm]![T1] etc. Just make the first query and the save as Export within the data base and give new name and then change criteria.

    I would then make a macro with 14 open query actions and with conditions being

    [Forms]![CarForm]![T1] Is Not Null
    [Forms]![CarForm]![T3] Is Not Null and [Forms]![CarForm]![T4] Is not Null

    and etc.

    I think all that will work OK.

    Mike

Posting Permissions

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