Results 1 to 2 of 2
  1. #1
    Lucas83 is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    6

    Query in visual basic

    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!!

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    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.

    Code:
       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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using Access Instead of Visual Basic
    By dinz in forum Programming
    Replies: 2
    Last Post: 12-19-2019, 10:33 AM
  2. Locking form with Visual Basic
    By rev_ollie in forum Forms
    Replies: 4
    Last Post: 04-29-2010, 07:27 AM
  3. Record Value in Visual Basic
    By chrismec in forum Programming
    Replies: 3
    Last Post: 12-09-2009, 04:14 PM
  4. microsoft visual basic REFERENCE
    By marianne in forum Access
    Replies: 0
    Last Post: 04-01-2009, 08:06 PM
  5. Visual Basic ToolBar and ImageList
    By Athanasopolous in forum Programming
    Replies: 0
    Last Post: 01-17-2006, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums