Results 1 to 7 of 7
  1. #1
    spacekowboy is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    17

    Syntax help on an Expression

    Hi, I have the following expression:



    ClaimAmtDue: IIf(TblMasterClaimsRecord![Claim Number] Like "14*","$200","$350")

    I also need the following incorporated into the expression but having trouble:

    if Type of Loss = "RPT" then $100 else $350

    Any help would be appreciated

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    incorporated into the same expression?

    so the type of loss is only relevant if the claim number DOES NOT start with 14?

    you'd have to have a nested iif like this:

    ClaimAmtDue: IIf(TblMasterClaimsRecord![Claim Number] Like "14*","$200",(iif([type of loss] = "RPT", "$100", "$350")))

  3. #3
    spacekowboy is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    17
    Actually the "type of loss" will always be relevant

    Thanks

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't know what that means.

    You've introduced two variables with two different results each

    1. Claim number (the two results are starts with 14 and doesn't start with 14)
    2. Type of Loss (the two results are the value is RPT or the value isn't RPT)

    in essence you have four possible outcomes (two variables with 2 possible results each)

    so what do you want your formula to result in for each possible outcome?

  5. #5
    spacekowboy is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    17
    if "14" = $250
    if "RPT" = $100
    anything else = $350

    Thanks

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    So should test for "14" first? That takes precedence over "RPT"?

    rpeare's suggestion in post 2 does that.

    Is it 200 or 250?
    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
    spacekowboy is offline Novice
    Windows XP Access 2003
    Join Date
    May 2012
    Posts
    17
    So far it looks like this will work!
    ClaimAmtDue: IIf(TblMasterClaimsRecord![Claim Number] Like "14*","$200",(iif([type of loss] = "RPT", "$100", "$350")))

    Many thanks rpeare
    and June7

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

Similar Threads

  1. Replies: 3
    Last Post: 07-13-2013, 12:11 AM
  2. Expression Builder Syntax Problem
    By maxmaggot in forum Queries
    Replies: 7
    Last Post: 06-14-2013, 08:29 AM
  3. Syntax error in query expression
    By That_Guy in forum Programming
    Replies: 3
    Last Post: 08-17-2012, 09:01 AM
  4. syntax in query expression #ERROR help!
    By manos39 in forum Queries
    Replies: 1
    Last Post: 02-26-2011, 12:21 PM
  5. Syntax error (comma) in query expression?
    By TheWolfster in forum Queries
    Replies: 5
    Last Post: 05-10-2010, 12:02 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