Results 1 to 10 of 10
  1. #1
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Question Is there any alternative tool to expression builder?

    Hi,
    I tried to search for a tool to review complicated expression on a query, which contains nested iifs & some functions with 10s of parentheses and brackets rather than expression builder but I didn't find except code editors like "Brackets" which doesn't contains access rules.
    Is there any professional way to review such expressions?


    Thank you.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One recommended method is to build your expression as a string so you can use a debug.print line to check its value.
    Then use the string in your code.

    In general using lots of nested Iif statements are best avoided as it's very difficult to get these right and to troubleshoot
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Notepad++ is free and can be handy for matching brackets. It colors matching brackets to assist syntax checking.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have to say no. No editor will have Access rules and libraries. If it did, it would BE Access.

    I just tested Notepad++ set to VB language (VBA is not listed) and seems best can do is click on each paren, bracket, brace to see where its mate sits and yes the coloring does help with visually checking structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    another potential solution is to simplify your expression - not always possible but in my experience is often caused by poor design. Some suggestions:

    store 'hard coded values' in a table - perhaps with the rule to be applied or the answer
    ensure all field names are unique (but still meaningful) and not repeated across tables necessitating the need to use the table name as a clarifier in the expression.
    Consider using a different function to iif - choose and switch come to mind.
    Ensuring that the expression is efficient - e.g. doesn't test for the same thing more than once - typically applies to ranges
    Ensure data brought through is null free so the expression does not have to test for nulls using nz function or iif(fld is null,...)

    Expressions can 'look' complicated due to using long table/field names - alias the tables to something shorter
    Expressions can also 'look' complicated by have spaces or non alpha numeric characters in names necessitating the use of square brackets
    Excessive use of round brackets can also make it difficult to interprete.

    All expression logic is either true or false so with a bit of paper you can construct the logic - ask a question, answer is true of false, for each of these outcomes ask another question. repeat until done. You may then see you are asking the same question more than once, or come up with the same answer by different routes -so reorganise how you ask the questions

    Another trick is to reverse the logic - start at the bottom rather than the top.

  6. #6
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks ridders52 for your advice.
    You are right avoiding nested expressions are recommended but my engineering project contains many conditions and requires many checks

  7. #7
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks Orange & June7.
    I didn't use Notepad++ but I used Bracket apps, & it contains checks for SQL code in addition to visually check parentheses and brackets.
    Brackets doesn't contain Access libraries to check functions but it display the reserved words on a different color.

  8. #8
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks Ajax for your suggestions specially alias the table - I didn't use it before
    Ensure data brought through is null free so the expression does not have to test for nulls using nz function or iif(fld is null,...)
    good idea to not check for nulls.

    Ensuring that the expression is efficient - e.g. doesn't test for the same thing more than once - typically applies to ranges
    I've question here, is a VBA customized function executed faster than using code inside the query?

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    In phone so limited response - it depends on the expression and how often called if called once for example as a criteria it may be faster but suspect not in this case

  10. #10
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks Ajax

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

Similar Threads

  1. About Expression Builder
    By notrino in forum Forms
    Replies: 5
    Last Post: 10-17-2018, 10:43 AM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  4. Expression builder help
    By jigg14 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 09:47 AM
  5. Expression Builder not there
    By schnuber in forum Access
    Replies: 1
    Last Post: 02-17-2012, 01:17 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