Results 1 to 4 of 4

Thread: SQL problems

  1. #1
    Join Date
    Nov 2005
    Posts
    113

    Unanswered: SQL problems

    Hello!
    I have 5 comboboxes where I list the value (of the type string) from five different columns in a table.

    The user can choose to search on one, two or every comboboxes in a question and press the button search.

    The problem is how I can build a question to be able to choose one or more fields? The fields that aren't chosen are empty (Null)

    Select field1, field2 field3, field4 field5 from table
    Where field1 like ' & cbo1 & " ' and field2 like ' " & cbo2 & " ' ...

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How about:

    WHERE field1 = nz(forms!yourForm!yourCbo, field1)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You'll probably want to do something like:

    Code:
        Dim strFilter As String
    
        'Initialize the clause.
        strFilter = ""
        
        'If the Paper Size is specified
        If Me.cboPaperSize & "" > "" Then
            
            strFilter = strFilter & " AND [PaperSizeID] = " & Me.cboPaperSize
            
        End If
        
        'If the Printer is specified
        If Me.cboPrinter & "" > "" Then
        
            strFilter = strFilter & " AND [Printer] =" & Me.cboPrinter
            
        End If
    This example only has two fields. For your example you would want to continue adding three more fields. Also, the names of the fields need to be changed for your situation.

  4. #4
    Join Date
    Nov 2005
    Posts
    113
    Thanks a lot for your answer.

    I shall try Nz first.

Posting Permissions

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