none
How to filter a sub form? RRS feed

  • Question

  • I have a sub form that has 4 fields, What I want to do is enter a word into a textbox and then filter any record that has that word in that field. eg: If I enter "Black" I will get "Black Horse" and "Black cat" with its corresponding fields

    Thanks for any help.............Bob


    xxx

    Thursday, September 15, 2016 12:23 AM

Answers

All replies

  • The following little demo may be of use to you to dissect.

    http://www.devhut.net/2014/08/21/ms-access-search-with-spaces-in-terms/


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, September 15, 2016 1:00 AM
  • Thanks Daniel that was a perfect demo to use :)

    have a problem with my query I am not getting unique records even when I have selected Yes

    Thanks for the help..Bob

    SELECT DISTINCTROW tblAdditionCharge.HorseID, tblAdditionCharge.AdditionCharge, tblAdditionCharge.AdditionChargeAmount, IIf(IsNull([HorseName]) Or [HorseName]='',[FatherName] & '--' & [MotherName] & ' -- ' & funCalcAge(Format('01-Aug-' & [DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1) & ' -- ' & [Sex],[HorseName]) AS HorseNamed, tblAdditionCharge.SpareDateTime
    FROM tblAdditionCharge INNER JOIN tblHorseInfo ON tblAdditionCharge.HorseID = tblHorseInfo.HorseID
    WHERE (((tblAdditionCharge.AdditionCharge) Is Not Null))
    ORDER BY tblAdditionCharge.SpareDateTime DESC;


    xxx

    Thursday, September 15, 2016 9:49 PM
  • I believe you selected Unique Values when in fact you want Unique Records.

    DISTINCTROW vs DISTINCT


    -- Daniel Pineault, 2010-2015 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, September 17, 2016 1:19 AM
  • Thanks Daniel, that didn't work DISTINCT

    Another thing I can not sort on a field that is a expression [HorseNamed]

    Thanks for the help..........Bob


    xxx

    Saturday, September 17, 2016 2:46 AM
  • You might also like to take a look at FindWords.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file a 'word' is any substring at the start or end of a string expression, or a substring delimited by a space or punctuation character before or after the substring.  The demo illustrates means of returning rows on the basis of a single word, or any or all of a set of words.

    As regards ordering a query's result table by a computed column you should do so on the expression, not on the column heading.

    Ken Sheridan, Stafford, England

    Saturday, September 17, 2016 10:58 AM