Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have this sql and have a dubt...
    But if one or more condition WHERE are with a var blank the query process only the value not blank and not consider the blank var...
    SSQL = "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA15,PROVA1 7,PROVA13,PROVA14,PROVA18 FROM DATI WHERE PROVA19 = '" & Left(Me.COMBO_DISTRETTO, 8) & "' AND PROVA20 = '" & Left(Me.COMBO_MERCATO, 8) & "' AND PROVA2 = '" & Mid(Me.COMBO_SPORTELLO, 10, 4) & "' AND PROVA3 = '" & Me.COMBO_SETTORE & "' ORDER BY LEFT(PROVA1,8)"

    in this case Me.COMBO_SPORTELLO is blank...

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='sal21' post='771534' date='21-Apr-2009 09:00']I have this sql and have a dubt...
    But if one or more condition WHERE are with a var blank the query process only the value not blank and not consider the blank var...
    SSQL = "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA15,PROVA1 7,PROVA13,PROVA14,PROVA18 FROM DATI WHERE PROVA19 = '" & Left(Me.COMBO_DISTRETTO, 8) & "' AND PROVA20 = '" & Left(Me.COMBO_MERCATO, 8) & "' AND PROVA2 = '" & Mid(Me.COMBO_SPORTELLO, 10, 4) & "' AND PROVA3 = '" & Me.COMBO_SETTORE & "' ORDER BY LEFT(PROVA1,8)"

    in this case Me.COMBO_SPORTELLO is blank...[/quote]
    I kind of doubt I'm the only one who didn't really understand what you are asking.

    First of all, what is "dubt"?

    Then, what does this mean "But if one or more condition WHERE are with a var blank the query process only the value not blank and not consider the blank var..."?
    You might want to give an example of what you are trying to say.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What do you want the query to return if a combo box is blank?

    Does this do what you want?

    Code:
    Dim strWhere As String
    
    If Not IsNull(Me.COMBO_DISTRETTO) Then
      strWhere = strWhere & " AND PROVA19 = '" & Left(Me.COMBO_DISTRETTO, 8) & "'"
    End If
    If Not IsNull(Me.COMBO_MERCATO) Then
      strWhere = strWhere & " AND PROVA20 = '" & Left(Me.COMBO_MERCATO, 8) & "'"
    End If
    If Not IsNull(Me.COMBO_SPORTELLO) Then
      strWhere = strWhere & " AND PROVA20= '" & Mid(Me.COMBO_SPORTELLO, 10, 4) & "'"
    End If
    If Not IsNull(Me.COMBO_SETTORE) Then
      strWhere = strWhere & " AND PROVA3 = '" & Left(Me.COMBO_SETTORE, 8) & "'"
    End If
    SSQL = "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA15,PROVA17,PROVA13,PROVA14,PROVA18 FROM DATI"
    If Not strWhere = "" Then
      SSQL = SSQL & " WHERE " & Mid(strWhere, 6)
    End If
    SSQL = SSQL & " ORDER BY LEFT(PROVA1,8)"

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='771581' date='21-Apr-2009 18:56']What do you want the query to return if a combo box is blank?

    Does this do what you want?

    Code:
    Dim strWhere As String
    
    If Not IsNull(Me.COMBO_DISTRETTO) Then
      strWhere = strWhere & " AND PROVA19 = '" & Left(Me.COMBO_DISTRETTO, 8) & "'"
    End If
    If Not IsNull(Me.COMBO_MERCATO) Then
      strWhere = strWhere & " AND PROVA20 = '" & Left(Me.COMBO_MERCATO, 8) & "'"
    End If
    If Not IsNull(Me.COMBO_SPORTELLO) Then
      strWhere = strWhere & " AND PROVA20= '" & Mid(Me.COMBO_SPORTELLO, 10, 4) & "'"
    End If
    If Not IsNull(Me.COMBO_SETTORE) Then
      strWhere = strWhere & " AND PROVA3 = '" & Left(Me.COMBO_SETTORE, 8) & "'"
    End If
    SSQL = "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA15,PROVA17,PROVA13,PROVA14,PROVA18 FROM DATI"
    If Not strWhere = "" Then
      SSQL = SSQL & " WHERE " & Mid(strWhere, 6)
    End If
    SSQL = SSQL & " ORDER BY LEFT(PROVA1,8)"
    [/quote]


    work naturally!

Posting Permissions

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