Results 1 to 4 of 4
  1. #1
    pcc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    5

    dlookup and iff in codesource

    i want item price to be selected from price table based on the size of quantity ordered



    =IIf([QTY]<=4,(DLookUp(" item_price","Price Table","[Price Table]![item_code]=" & [code] & "And [Price Table]![item_size]=10")),(IIf([QTY]<=15,(DLookUp(" item_price","Price Table","[Price Table]![item_code]=" & [code] & "And [Price Table]![item_size]=20")),(DLookUp(" item_price","Price Table","[Price Table]![item_code]=" & [code] & "And [Price Table]![item_size]=30")))))

    worked for me but it is too long ca someone simplify.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Duplicate of the other thread ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    =DLookUp("item_price", "Price Table", "[Price Table]![item_code]=" & [code] & " And [Price Table]![item_size]=" & Switch([Qty]<=4, 10, [Qty]<=15, 20, True, 30))
    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.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Except the OP changed the criteria ...
    June answered one and I did the other ...!
    Didn't think of using Switch - I tend to forget that ....
    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

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

Similar Threads

  1. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  2. DLookup help
    By vicsaccess in forum Programming
    Replies: 2
    Last Post: 01-10-2016, 11:35 AM
  3. DLookup with value from CBO
    By excellenthelp in forum Access
    Replies: 6
    Last Post: 12-31-2014, 09:11 AM
  4. Dlookup
    By pcandeias0 in forum Programming
    Replies: 3
    Last Post: 07-09-2011, 02:31 PM
  5. DLookup help
    By denners05 in forum Access
    Replies: 1
    Last Post: 06-11-2011, 12:55 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