Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    7

    Unanswered: lista.RowSource = "select * from x And And And And"

    Hello ALL,
    i have a list box linked to a table, and in the form where i have the list i also have 10 textbox's to work as filters.
    The first 2 conditions work perfectly:
    TxtNome.SetFocus
    If TxtNome <> "" Then lista.RowSource = "select * from fichaindividual where [nome] like '*" & Me.TxtNome & "*';"
    TxtCap.SetFocus
    If TxtCap <> "" Then lista.RowSource = "select * from fichaindividual where [nome] like '*" & Me.TxtNome & "*' and capacidademax >= txtcap.text and capacidademin <= txtcap.text;"

    The problem comes in the 3rd condition (textbox).
    TxtPreçoPub.SetFocus
    If TxtPreçoPub <> "" Then lista.RowSource = "select * from fichaindividual where [nome] like '*" & Me.TxtNome & "*' and [capacidademax] >= txtcap.text and [capacidademin] <= txtcap.text and [preçopub] <= txtpreçopub.text;"

    Where i get a blank list box lol (but the code of the solo 3rd textbox works
    TxtPreçoPub.SetFocus
    If TxtPreçoPub <> "" Then lista.RowSource = "select * from fichaindividual where [preçopub] <= txtpreçopub.text;"

    I think its something about the AND's placement... Can somone help plz

    Thz in advance

    (working in Access 2010)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    1. To retrieve the value of a control, use it's Value property, not its Text property:
    Code:
    [capacidademax] >= txtcap.text
    [preçopub] <= txtpreçopub.text
    Shoul become:
    Code:
    [capacidademax] >= txtcap..Value
    [preçopub] <= txtpreçopub.Value
    That way, you don't need to set the focus on a control before retrieving its value.

    2. Did you check that there are records that match the 3 conditions simultaneously?
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    7
    Hi sin, thz about the .value/.text issue.

    Yup i've checked the conditions.

    Theres any kind of limit in the conditions of the SELECT command ? (because i try with 3 simple conditions with AND) but more then that doesnt work anymore.

    Ex:
    Private Sub cmdListIndex_Click()
    Comboproj.SetFocus
    txtproj.Value = Comboproj.Text
    lista.RowSource = "select * from listagem where [proje] like '*" & Me.txtproj & "*';"
    Combofase.SetFocus
    If Combofase.Text <> "" Then
    txtfase.Value = Combofase.Text
    lista.RowSource = "select * from listagem where [proje] like '*" & Me.txtproj & "*' and [fase] like '*" & Me.txtfase & "*';"
    End If
    Comboespec.SetFocus
    If Comboespec.Text <> "" Then
    txtespec.Value = Comboespec.Text
    lista.RowSource = "select * from listagem where [proje] like '*" & Me.txtproj & "*' and [fase] like '*" & Me.txtfase & "*' and [especialidade]

    like '*" & Me.txtespec & "*';"
    End If
    End Sub

    If i add another condition (AND) it stop working retrieveing me always an empty listbox. (even with the conditions being true simultaneously)

    ;(

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can have as many terms in your where clause as you need, I don't know if there is a hard coded limit in JET, but I've never hit it (yet)

    its far more likely there is a glitch in your SQL
    ..do you actually mean to use AND as opposed to say OR?
    ...have you checked that there are rows that could be returned from the SQL you are sending

    what I'd suggest you do is examine the SQL your are sending to the engine isd 'sane'.
    either set a watch / breakpoint or assign the SQL to a variable and use something like msgbox() to display the contents of that variable.

    another approach is to re-write your SQL to somethign like...

    Code:
    dim strSQL as string
    strSQL = "select * from listagem where 1=1" 'this dodge means you will always have valid where clause
    if len(comboproj.value)>0 then 'test if there is a value set
      'you don't need to use square brackets to delimit table / column names if you don't use spaces in column names
      strSQL =  strSQL & " AND proje like '*" & Comboproj.value & "*';"
    endif
    If len(Combofase.value)>0 then
      strSQL =  strSQL & " AND fase like '*" & Combofase.value & "*';"
    End If
    If len(Comboespec.value) >0  Then
      strSQL =  strSQL & " AND especialidade like '*" & Comboespec.value & "*';"
    endif
    End If
    msgbox (strSQL, vbinformation, "My Sql is") 'whilst debugging
    lista.rowsource = strSQL
    lista.refresh ' may not need this, or may need lista.requery
    End If
    you ma need to also test for not null
    eg:-
    Code:
    If not isnull(Comboespec.value) AND len(Comboespec.value) >0  Then
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    7
    Quote Originally Posted by healdem View Post
    you can have as many terms in your where clause as you need, I don't know if there is a hard coded limit in JET, but I've never hit it (yet)

    its far more likely there is a glitch in your SQL
    ..do you actually mean to use AND as opposed to say OR?
    ...have you checked that there are rows that could be returned from the SQL you are sending

    what I'd suggest you do is examine the SQL your are sending to the engine isd 'sane'.
    either set a watch / breakpoint or assign the SQL to a variable and use something like msgbox() to display the contents of that variable.

    another approach is to re-write your SQL to somethign like...

    Code:
    dim strSQL as string
    strSQL = "select * from listagem where 1=1" 'this dodge means you will always have valid where clause
    if len(comboproj.value)>0 then 'test if there is a value set
      'you don't need to use square brackets to delimit table / column names if you don't use spaces in column names
      strSQL =  strSQL & " AND proje like '*" & Comboproj.value & "*';"
    endif
    If len(Combofase.value)>0 then
      strSQL =  strSQL & " AND fase like '*" & Combofase.value & "*';"
    End If
    If len(Comboespec.value) >0  Then
      strSQL =  strSQL & " AND especialidade like '*" & Comboespec.value & "*';"
    endif
    End If
    msgbox (strSQL, vbinformation, "My Sql is") 'whilst debugging
    lista.rowsource = strSQL
    lista.refresh ' may not need this, or may need lista.requery
    End If
    you ma need to also test for not null
    eg:-
    Code:
    If not isnull(Comboespec.value) AND len(Comboespec.value) >0  Then
    I dont know SQL ;(, all i do is in Access "VBA - Visual basic i think"

    And i want to make AND instead of OR.

    And its A + A = Result (less records)
    Or its A or B = Diferent result (more records)
    (if im not wrong)

    Thz

  6. #6
    Join Date
    Nov 2011
    Posts
    7
    I think i figured out Where's the problem.

    In this conditions (capacidademax >= txtcap.value and capacidademin <= txtcap.value) when i leave txtcap empty it origins the empty list box.

    With this code:
    If TxtPreçoPub.Value > 0 Then
    lista.RowSource = "select * from fichaindividual where nome like '*" & Me.TxtNome & "*' and capacidademax >= txtcap.value and capacidademin <= txtcap.value and preçopub <= txtpreçopub.value;"
    End If

    How can i avoid this problem? Can i put If's inside the select? If i do how

    Tvm

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check for an appropriate value in the list box, if there is no value don't include that term in the sql

    Access uses three things concurrently
    theres the form and report wizards
    theres code that optionally can be put behind form and report events
    theres SQL which manipulates the data, whetehr you use the query designer, list or combo boxes, bound controls what ever. Access gets its data using SQL. the code you psoted is VBA but it also contains SQL. the select * from fichaindividual where [nome] like '*" & Me.TxtNome & "*';" is SQL.

    think of it as a two step process you have VBA code that handles stuff inside forms and reports in Access, there's SQL which does something to or with data. behind the scenes Access is issuing all manner of SQL statements to retrieve data (thats why you specify a row source that refers to a tabls or query, under the hood thats a query that Access uses to retrieve the data.

    the code I posted could be put inside your list box click event replacing your exisiting code and building where clause of the SQL as required. doing it the way I suggest means you gradually build the SQL, rather than creating a brand new sql statement each time
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    7
    Quote Originally Posted by healdem View Post
    check for an appropriate value in the list box, if there is no value don't include that term in the sql

    Access uses three things concurrently
    theres the form and report wizards
    theres code that optionally can be put behind form and report events
    theres SQL which manipulates the data, whetehr you use the query designer, list or combo boxes, bound controls what ever. Access gets its data using SQL. the code you psoted is VBA but it also contains SQL. the select * from fichaindividual where [nome] like '*" & Me.TxtNome & "*';" is SQL.

    think of it as a two step process you have VBA code that handles stuff inside forms and reports in Access, there's SQL which does something to or with data. behind the scenes Access is issuing all manner of SQL statements to retrieve data (thats why you specify a row source that refers to a tabls or query, under the hood thats a query that Access uses to retrieve the data.

    the code I posted could be put inside your list box click event replacing your exisiting code and building where clause of the SQL as required. doing it the way I suggest means you gradually build the SQL, rather than creating a brand new sql statement each time
    I think if i follow your example with the string it will work, since will avoid the condition capacidademax/camacidademin if the txtcap.value is empty, im going to try, (im just trying to understand how i will "merge the string etc)

    Thz alot heal, i will report in a bit hehe.

  9. #9
    Join Date
    Nov 2011
    Posts
    7
    HEALLL... i would pay you a bear if you were near ;P

    This is the final code:
    Private Sub search_Click()
    Dim strsql As String
    strsql = "select * from fichaindividual where 1=1"
    lista.RowSource = strsql
    If Len(TxtNome.Value) > 0 Then
    strsql = strsql & " AND nome like '*" & TxtNome.Value & "*'"
    End If
    If Len(TxtCap.Value) > 0 Then
    strsql = strsql & " AND capacidademax >= txtcap.value and capacidademin <= txtcap.value"
    End If
    If Len(TxtPreçoPub.Value) > 0 Then
    strsql = strsql & " AND preçopub <= txtpreçopub.value"
    End If
    ''msgbox (strSQL, vbinformation, "My Sql is") 'whilst debugging
    ''lista.RowSource = strsql
    ''lista.Refresh ' may not need this, or may need lista.requery
    lista.RowSource = strsql
    End Sub

    But i had to remove the dot and slash ( from the end of the expressions

    strsql = strsql & " AND fase like '*" & Combofase.Value & "*';"

    Because wasnt summing ;P.

    Thz alot, you saved my day

Posting Permissions

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