Results 1 to 3 of 3

Thread: Search Data….

  1. #1
    Join Date
    Aug 2003
    Location
    As of Now I'm working in KSA
    Posts
    1

    Unanswered: Search Data….

    As of the moment I’m working with one form with subform in Access 2000. This form I’ve made is that, there is six unbound text boxes(txt1, txt2, txt3, txt4, txt5, & txt6), 1 command button named “Searched” and the source of the record was taken from one Query with fields correspond to the six text boxes (fld1, fld2, fld3, fld4, fld5, & fld6). What I want on this form is everytime I fill-in the unbound text boxes and if I will click the “Searched’ it will automatically search that data I filled-in on my Query and it will display the information equal to that data in the subform. My question and problem is that what if the users fill-in data in unbound text boxes we can say it can be the users fill-in only the data in the txt1 or txt2, it can be txt3 and txt4 and txt1 & so forth, its up to the users. What I want is whatever the combinations of the users fill-in data in the unbound text boxes and this data will satisfy the criteria and it has a record it will automatically display to the subform, if not it just display message “Record Not found”. I want to ask for the help in coding or any idea related to this problem. I’ve found an idea but it takes very long codes using “Nested If”. So I decide to find something short and more simplify.

    More Power to all dbForums members

    jasanph

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Assuming your table is named MyTable, and your for is named MyForm (make appropriate changes if this isn't the case):

    Use one of these queries as the recordsource for your subform, then requery the subform in the Click event of the search button.

    This query will display records that have the search criteria anywhere within the field.

    SELECT [MyTable].*
    FROM [MyTable]
    WHERE (
    AND ((([MyTable].[fld1]) Like "*" & [Forms]![MyForm]![txt1] & "*") Or IsNull([Forms]![MyForm]![txt1]))
    AND ((([MyTable].[fld2]) Like "*" & [Forms]![MyForm]![txt2] & "*") Or IsNull([Forms]![MyForm]![txt2]))
    AND ((([MyTable].[fld3]) Like "*" & [Forms]![MyForm]![txt3] & "*") Or IsNull([Forms]![MyForm]![txt3]))
    AND ((([MyTable].[fld4]) Like "*" & [Forms]![MyForm]![txt4] & "*") Or IsNull([Forms]![MyForm]![txt4]))
    AND ((([MyTable].[fld5]) Like "*" & [Forms]![MyForm]![txt5] & "*") Or IsNull([Forms]![MyForm]![txt5]))
    AND ((([MyTable].[fld6]) Like "*" & [Forms]![MyForm]![txt6] & "*") Or IsNull([Forms]![MyForm]![txt6]))
    );


    This query will display records that exactly match the search criteria for the field.

    SELECT [MyTable].*
    FROM [MyTable]
    WHERE (
    AND ((([MyTable].[fld1]) = [Forms]![MyForm]![txt1]) Or IsNull([Forms]![MyForm]![txt1]))
    AND ((([MyTable].[fld2]) = [Forms]![MyForm]![txt2]) Or IsNull([Forms]![MyForm]![txt2]))
    AND ((([MyTable].[fld3]) = [Forms]![MyForm]![txt3]) Or IsNull([Forms]![MyForm]![txt3]))
    AND ((([MyTable].[fld4]) = [Forms]![MyForm]![txt4]) Or IsNull([Forms]![MyForm]![txt4]))
    AND ((([MyTable].[fld5]) = [Forms]![MyForm]![txt5]) Or IsNull([Forms]![MyForm]![txt5]))
    AND ((([MyTable].[fld6]) = [Forms]![MyForm]![txt6]) Or IsNull([Forms]![MyForm]![txt6]))
    );

    Does that help jasan?
    JT

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Thumbs up

    J.T.

    Muy bueno lo tuyo, muy ingenioso. ( Very Good ).

    Norberto
    Saludos
    Norberto

Posting Permissions

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