Results 1 to 7 of 7
  1. #1
    Kirnicko is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    8

    Conditional Format assistance

    Hi all! I have an Access database with numerous tables, queries, reports etc. I'm comfortable with the program but am struggling to find detailed information on how to do key word search in a field for conditional formatting. When researching conditional format help, they are VERY specific parameters, i.e Less than 100. Or if field matches "Robert". I need something a little more flexible. I have a text field that is unlimited size. Notes are kept in the field reference automobile maintenance. So if I put "oil change completed" or "brakes grinding. Replaced front and rear". I need to highlight the report field so that anything with "oil", or "b service" or "oil change" will highlight Blue. Or "brakes", "brake" highlight green.

    So essentially I need to figure out how to tell system to search the entire field and if "oil" shows up, (condition), then shade blue (format). If anyone has a link to a detailed site that expands on this I would really appreciate it! Or if anyone can explain or show examples. Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,330
    Try a condition of:

    [FieldName] Like "*Oil*"

    You can add others separated by OR if that works.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,956
    While you can enter all kinds of data in a Memo (Long Text) Field, one of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customer visit or a doctor's progress notes on a patient.

    These fields have are considered to be problematic by many developers, but working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one single rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

    Don't know how deep into this database you are...but if possible you really should be using a Subform to enter the various maintenance procedures...with one procedure per Record. It'll save you a lot of heart ache in the end. Right now you're just trying to identify one piece of data in one Record...one day you'll need to know how many oil changes you've done in a given time period...or brake jobs, etc. And it'll be a whole lot easier doing this searching a single Field for a given Value than having to slog through a much larger Table with a text field that is unlimited size.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Kirnicko is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    8
    THank you so much for replies. Unfortunately I'm YEARS into this database and am too late to start over. FORTUNATELY, vehicles are only kept for certain amount of time so any searches are relatively few entries. (not thousands or anything). Luckily, I had stumbled upon the suggestion pbaldy had above and I've been able to do exactly what was needed with this method, it just seems I've tapped out. I'm unable to capturn any more items. I DO separate each entry by type of service. But we recently decided we needed to watch strut damage and when I tried to do the LIKE sample above, there was no response from system. Conditional formatting did not produce results. Wondering if there is a "limit", and are you able to show the string to include "and" i.e. Like oil AND B serive AND oil change? I'm struggling with the exact formula

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,330
    Along the lines of

    [FieldName] Like "*Oil*" OR [FieldName] Like "*B serive*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Kirnicko is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    8
    Thank you pbaldy! I will try that today!

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,330
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Format using another field
    By sgtclark in forum Forms
    Replies: 3
    Last Post: 03-04-2020, 08:46 AM
  2. Conditional report format in vba
    By skyatis in forum Reports
    Replies: 4
    Last Post: 01-20-2017, 01:00 AM
  3. Conditional Format question
    By JimO in forum Access
    Replies: 3
    Last Post: 06-10-2016, 05:13 PM
  4. combobox + conditional format
    By markjkubicki in forum Forms
    Replies: 4
    Last Post: 06-24-2014, 04:25 PM
  5. Conditional number format
    By terryvanduzee in forum Forms
    Replies: 1
    Last Post: 07-14-2011, 01:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums