Results 1 to 7 of 7
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    130

    tax code conversion problem

    I've got a query that i want to update a field to a new value depending on what is originally stated in the field.



    currently for my field [hst code for qb] I either have "Z" or "H" - this represents zero rated or hst applicable - and I realize i shouldn't have field names with spaces....this was committed to many years ago unfortunately.....

    if i use the expression in my query as follows:


    x: IIf([hst code for qb]="z","Zero-rated",[hst code for qb])


    any record with the [hst code for qb] set to "z" converts to "Zero-rated" perfectly.

    When i try to combine both i get errors, so i know i'm missing something in the AND / OR part of the expression.


    x: IIf([hst code for qb]="z","Zero-rated",[hst code for qb]) And IIf([hst code for qb]="h","HST ON",[hst code for qb])


    Can someone show me how to combine these two expressions into one larger one that i can use in my query?

    thanks in advance.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    You usually nest IIF functions. The premise is that the inner one gets evaluated first and returns one of two values, then the outer one deals with that result vs the other option from the outer IIF. Care to take a crack at it yourself (or look up nested IIF's)?
    Last edited by Micron; 05-28-2020 at 01:22 PM. Reason: correction
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,278
    IIF is ok for 1 time, but if youre going to use more, make a table and convert by join them in the query.

  4. #4
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    130
    I see what you're saying.... let me play around with this.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,264
    you could use the switch function

    x: Switch([hst code for qb]="z","Zero-rated",[hst code for qb]="h","HST ON", true, [hst code for qb])

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    Quote Originally Posted by Micron View Post
    You usually nest IIF functions. The premise is that the inner one gets evaluated first and returns one of two values, then the outer one deals with that result vs the other option from the outer IIF. Care to take a crack at it yourself (or look up nested IIF's)?
    That makes sense for IIf() in VBA because all expressions must be able to evaluate or the whole thing returns error.

    In query, I have a different perception of IIf(). I see it as 'drilling in'. Starts with the outer IIf() and if it is true returns that true value if not then it returns the false value and if that is another expression it gets evaluated. It only evaluates an expression when it has to.

    This expression will work in query if x is given value of 1. In VBA it would cause run-time error even if x has value of 1 because 2/0 cannot evaluate.
    IIf([x] = 1, 2/1, 2/0)
    Last edited by June7; 05-29-2020 at 12:37 AM.
    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.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    That is interesting. It's like it stops evaluating in a query as soon as a portion resolves to True, so I expect that would be the case no matter how many levels you nest.
    Good to know.

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

Similar Threads

  1. Problem with date conversion
    By WAVP375 in forum Access
    Replies: 2
    Last Post: 07-07-2018, 02:02 PM
  2. UPDATE queries problem : type conversion failure
    By Michail_arkov in forum Queries
    Replies: 3
    Last Post: 10-23-2017, 03:32 AM
  3. Replies: 12
    Last Post: 08-23-2016, 09:22 AM
  4. Code problem
    By CHEECO in forum Access
    Replies: 3
    Last Post: 04-18-2016, 08:05 PM
  5. Replies: 3
    Last Post: 08-17-2012, 02:01 PM

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