Query in visual basic

[Lucas83]

Hi Everyone,

well I'm stuck with some access issue. I have a few textboxes and I want to carry out a query using textboxes as criteria. When the user fill some of the textboxes, the user click the button, the form carry out the query and the results are displaying in a listbox.

I decided to use click event in the button and I put this SQL statment:

Lista12.RowSource = "SELECT DISTINCT Terreno.terreno " & _
" FROM (((Pais INNER JOIN Ciudad ON Pais.Idpais = Ciudad.Idpais) INNER JOIN Terreno ON Ciudad.Idciudad = Terreno.Idciudad) INNER JOIN Forma ON Terreno.Idterreno = Forma.Idterreno) INNER JOIN (Parcela INNER JOIN contratoparcela ON Parcela.Idparcela = contratoparcela.Idparcela) ON Terreno.Idterreno = Parcela.Idterreno " & _
" WHERE (((Pais.pais)= '" & Me!Texto0 & "') and ((Ciudad.ciudad)= '" & Me!Texto2 & "'));"



The SQL comes from a query I created, I copied the SQL statement and pasted it in the click event. It works but not properly. When I use "and" in where statement, I need to fill all textboxes because If I left any of the textboxes in blank, the listbox doesn't display anything (I understand when I use "and· the SQL statement needs all criteria) however when I use this same statement in my query in access and I left any criteria in blank, the query shows results using the other criteria. I guess I need to indicate to visual basic that if a textbox is in blank, It doesn't need to take it into account. Does anyone know any solution? or if you had another way to do the same, I'm open to any suggestion. Thank you very much!!

[Rawb]

Use an If statement (or a switch) to check if your text boxes all have information in them. Then, based on which ones have text entered, chanve the Query.

   If IsNull(Me!Texto0) And IsNull(Me!Texto2) Then
    ' Query with only neither Texto0 or Texto2 as Criteria
  ElseIf IsNull(Me!Texto0) Then
    ' Query with only Texto2 as Criteria
  ElseIf IsNull(Me!Texto2) Then
    ' Query with only Texto0 as Criteria
  End If

  ' Run Query