Expanding search parameters

[Felwick]

I'm a novice at navigating Access. I'm using the Contacts template in Access 2013 as it is comes with no alterations. My problem is as follows: When I use the search field at the top of the database, it only searches the first 6 rows of each column (first name, last name, Company, Job Title, Category, and Email). What I need it to do is include the "Notes" field in its search parameters.



After initiating a search, little icons (in the shape of a Y) appear on the first 6 rows, noting the rows that are being searched (I assume). When I mouseover one of these icons it gives me the column name, followed by the word "like" and whatever I've typed into the search bar (ie: [First Name] Like "*a*"), "a" being what I typed into the search bar.. The following link illustrates the problem I'm having. http://www.geocities.ws/felwick/Access%20Capture.PNG

Would anyone be able to tell me how to add more rows to the search form, to include the "Notes" row?

[ranman256]

1. If NOTES field is a memo field, you cant. You cant query memo fields.

2. the query property TOP is probably set to 6. Set it to ALL to get all records.

[CJ_London]

the 'Y' is a funnel and indicates that a filter has been applied to that column (not rows).

To add more columns to the filter, you need to modify the code that creates the filter. I have no idea whether that is a macro or vba code, but when you find it, the filter string will be something like

[First Name] Like '*' & me.txtSearch & '*' OR [Last Name] Like '*' & me.txtSearch & '*' OR ......

Just add to the end of that string

OR [Notes] Like '*' & me.txtSearch & '*'

using the same construction that already exists

Note that if Notes is a memo (long text) field then only the first 255 characters will be searched

To find it, go into form design view. click on your search control and open the properties (on the ribbon), then select the events tab - click on the 3 dot caret to the far right in (probably) the afterupdate or change event - you will see something like [Event Procedure] or [Embedded Macro] against the events that have code associated with them

[Felwick]

Thank you guys so much for your responses.. I've been toying around with this database at work, in my spare time. I know very little about Access apart from what I'm picking up in online tutorials and forums like this.

I'm in retail sales and trying to come up with a database of all our clients and their purchases. Roughly 300 customers a year with a purchase of about $5,000 per customer. So, it won't be a list with thousands of entries, only about 300. I'm using the "Desktop Contacts Template" right now and just adding their purchases to the "Notes" section of each contact entry. It's not ideal, but it's been working for me.

Unfortunately, since the search bar won't find anything typed into the notes column (since it's a memo field), I'm unable to type in a product name and get just a list of people who purchased a single product. But, if the notes column will search up to 255 characters, that'll be all the space I'd need. 255 characters is sufficient..

So, I'm following up to that point. I'm afraid I'm not fully understanding how to make the necessary changes to include the notes section (up to 255 characters).

ranman256: I'd love it if there turns out to be one area that I can just change from "6" to "all" and my problem is solved. Any idea where I find the "query property TOP"?

Ajax: " OR [Notes] Like '*' & me.txtSearch & '*' " ..That definitely sounds right. I'm not sure that I'll input it in all the right spots, but that definitely sounds like you're on the right track. Each of the fields that are being searched have that string in common. I just don't know where to find the complete string to input the addition.

But this is the closest I've come to an answer, so thank you for the help!

[ssanfu]

the query property TOP is probably set to 6. Set it to ALL to get all records.

Because of the filtering, there are only 6 records that match ALL of the fields being filtered. (no query/ no code that set a filter.)

If you left click on the funnel (the 'Y'), you will be able to see the text filter (Equals, Does Not Equal, etc) and the values to filter on. Do this for each field.

If you look at "HOME" on the menu bar, look at the "Sort & Grouping" group.
Click in a field, then click on the big filter (the 'Y') in the menu to select values to filter that field by.

To turn on or off the filtering, click on "Toggle Filter" in the menu.
Click on "Advanced" to see other filter options (Clear All filters", Apply Filters,...)