Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: How do i search using several Text boxes?

    Hi guys,

    ************(Re Type)

    I am trying to search by 3 txtboxes txtUserID, txtWeek & txtJobType i have a command button that when i press it it will display the results in a form.

    I know how to search by using AND which it requires the textbox for each to have data.... I also can use OR, but that still isnt exactly what I want to do. What I am trying to do is to let users enter data into textboxes but if they leave any of them blank i i want the search to ignore the txt box.

    Any ideas on how to do this?


    Aboo
    Last edited by aboo; 04-22-09 at 20:31.

  2. #2
    Join Date
    May 2006
    Posts
    178
    Also is it possible to display these results in a list box?


    Aboo
    Last edited by aboo; 04-22-09 at 20:30.

  3. #3
    Join Date
    May 2006
    Posts
    178
    Again, How would i display the results in a list box?

    Can someone please help

    Thanks

    Aboo

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You'd have to dynamically set the SQL of the listbox to be the results you want.

    In your VBA code, you would use IF to determine whether or not to add the criteria for each field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2006
    Posts
    178
    Thanks Trekker,


    Any pointers? or can you point me to some examples please.


    Thanks

    Aboo

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't know of where any examples of this might be, but you may not need any.

    The code would be something like this:

    Code:
    Dim strSQL as String
    
    If Me.ControlName > "" Then strSQL = "FieldName = " & Me.ControlName
    
    If Me.ControlName2 > "" Then
       If strSQL > "" Then
          strSQL = strSQL & " AND FieldName2 = " & Me.ControlName2
       Else
          strSQL = "FieldName2 = " & Me.ControlName2
       End If
    End If
    
    If Me.ControlName3 > "" Then
       If strSQL > "" Then
          strSQL = strSQL & " AND FieldName3 = " & Me.ControlName3
       Else
          strSQL = "FieldName3 = " & Me.ControlName3
       End If
    End If
    
    Me.ListBoxControlName.RowSource = "SELECT * FROM TableNAme WHERE " & strSQL
    
    Me.ListBoxControlName.Requery
    Disclaimer: This code was written just now from memory - it might need a bit of debugging!
    Last edited by StarTrekker; 04-23-09 at 05:21.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2006
    Posts
    178
    Hi Trekky,

    Thank you for your help and guidance, i managed to jiggle around the code and use it for my needs. It has worked a treat.

    thanks for taking some time out and helping.

    much appreciated


    Aboo

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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