Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2014
    Posts
    7

    Unanswered: Multi-line search form

    Hi folks,
    First of all, I'm very happy to enter in this community. I'm pretty sure that I will find a lot of knowledge and I will try to help everytime I can.

    This is my case:

    For weird reasons, in my new job, the business rule is use Excel and Access only solutions. I have some expertise working with some DBMS like MySQL/MSSQL and developing webforms in PHP. The point here is, I'm "forced" to work with forms in access. The problem here is: I don't have any expertise working with Access

    So, I'm trying to create a simple form to search multiple values in one text field. I want to paste the values in the text field (every row contains a single record), and display the query result. I don't have any problem to show the query results, but I'm stuck to make that Access read every single row to find every record.

    Thanks in advance for your help.

    Cheers!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have to think in terms of sets. Retrieving rows from a data set where a column in this data set matches a searched values consists in writing a query such as (for a Text SearchField and a value:
    Code:
    SELECT Field1, ... FROM TableName WHERE SearchField = 'SearchValue';
    If the searched value can only be a part of the contents in SearchField, you can use wildcards:
    Code:
    SELECT Field1, ... FROM TableName WHERE SearchField = '*SearchValue*';
    If the value must be searched in more than one field, you combine the conditions with a logical OR operator:
    Code:
    SELECT Field1, ... FROM TableName WHERE (SearchField1 = '*SearchValue*') OR (SearchField2 = '*SearchValue*');
    If you want to search for several values in a field, you use:
    Code:
    SELECT Field1, ... FROM TableName WHERE (SearchField = '*SearchValue1*') OR (SearchField = '*SearchValue2*');
    Note: Field1 and SearchField can be the same column or can be different columns and you can ask for more then one field in the returned data set
    Code:
    SELECT Field1, Field2, etc. FROM... WHERE...
    Depending on what you want to do with the rows matching the search, you can:
    - Assing the full SQL expression to the RecordSource property of a form.
    - If you have a form already bound to the full data set (table or query), you can use the conditional part or criteria (WHERE...) as a filter (without the WHERE statement):
    Code:
    Me.Filter = SearchField1 = '*SearchValue*'
    - Open a Recordset to process the data set returned by the query:
    Code:
    Dim rst As Dao.Recordset
    Dim strSQL As String
    strSQL = "SELECT Field1, ... FROM TableName WHERE SearchField = '*SearchValue*';"
    Set rst = CurrentDb.OpenrecordSet(strSQL, dbOpenDynaset) ' Use dbOpenSnapshot if you don't indend to modify the data returned by the query.
    With rst
        Do Until .Eof
            ' Process the data set.
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    - Retrieve the contents of the data set in an array of variants:
    Code:
    Dim rst As Dao.Recordset
    Dim strSQL As String
    Dim var As Variant
    strSQL = "SELECT Field1, ... FROM TableName WHERE SearchField = '*SearchValue*';"
    Set rst = CurrentDb.OpenrecordSet(strSQL, dbOpenSnapshot)
    With rst
        If .Eof = False Then var =  .GetRows(9999) ' 9999 rows max., adjust if necessary.
        .Close
    End With
    Set rst = Nothing
    - Of course, once you have the array of variants, you can process it too but modifying the values in the original table is more complex.
    Have a nice 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
  •