Results 1 to 4 of 4

Thread: Query conditions

  1. #1
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116

    Query conditions

    Ok, I'm assuming this uses a creative application of IIF(), but we'll see.

    I'm setting up a search form, and it will have I believe 4 search combo-boxes and/or text boxes. These choices are going to be the criteria on a query. The problem is, the user may or may not have enough information to make selections in all of them. Is there a way to set a criteria so that if the value it recievs is null it ignores it completly?

    I know how to look to see if its null, I just need to know how to set it up so its not used.

    Brandon

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Say for example you have 4 text boxes.
    this is what I do.

    Private Sub Command0_Click()
    Dim query As String
    Dim query2 As String

    If IsNull(Me!Text1) = False Then
    query2 = query2 & " Column1 = '" & Me!Text1 & "'"
    End If

    If IsNull(Me!Text3) = False Then
    query2 = query2 & " and Column2 = '" & Me!Text3 & "'"
    End If

    If IsNull(Me!Text5) = False Then
    query2 = query2 & " and Column3 = '" & Me!Text5 & "'"
    End If

    If IsNull(Me!Text7) = False Then
    query2 = query2 & " and Column4 = '" & Me!Text7 & "'"
    End If


    query = "select * from tablename where " & query2
    query = Replace(query, " where and ", " where ")

    If LTrim(RTrim((query2))) = "" Then
    query = Replace(query, "where", "")
    End If

    MsgBox query


    End Sub

  3. #3
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    Ok, that makes a lot of sense. One slight hitch though, the query I need to put the criteria in is already made. Not overly a big deal, and I would just drop all the SQL code from it in the approipriate places, except, its got a lotta different tables and fields, and it made Query WAY over the size limit of a string. :P

    Any hints on how to fix this speed bump?

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create 2 or 3 strings. and combine it before executing.

Posting Permissions

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