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

    dlookup with iif

    hi everyone


    i want item price from price table according to the qty size ordered.

    =IIf([QTY]<=3,(DLookUp(" item_price","Price Table","[Price Table]![item_code]=" & [code] & "And [Price Table]![item_size]=10")),(IIf([QTY]<=10,(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 can someone simplify it.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That will be horrendously slow if you have more than a few dozen records. You will be performing three separate queries on every line in the table.
    You shouldn't ever use domain functions in a query , as they can nearly always be replace by a join to the relevant table.

    Can you post up your tables design and/or some sample data, as this can be better organised.

    As a hint using the join to your price table you can use the IIf to simply return the correct size, without the lookup.
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Lots of possible solutions. Here are three to choose from...

    NOTE: I've had to replace [code] with [YourCode] to get round issues with forum code tags

    Replace MyValue with your own variable

    Code:
    Select Case Qty
    
    Case Is <=3
        MyValue = DLookUp("item_price","Price Table","[Price Table]![item_code]=" & [YourCode] & "And [Price Table]![item_size]=10")
    
    Case Is <=10
        MyValue = DLookUp("item_price","Price Table","[Price Table]![item_code]=" & [YourCode] & "And [Price Table]![item_size]=20")
    
    Case Else
        MyValue = DLookUp("item_price","Price Table","[Price Table]![item_code]=" & [YourCode] & "And [Price Table]![item_size]=30")
    
    End Select
    Or ...
    As above but using If ... ElseIf ... Else...End If

    Or
    Code:
    Dim intSize As Integer
    
    intSize = IIf([QTY]<=3,10,IIf([QTY]<=10,20,30)) 'air code - check the bracketing
    
    MyValue = DLookUp("item_price","Price Table","[Price Table]![item_code]=" & [YourCode] & "And [Price Table]![item_size]=" & intSize)
    Personally, I'd use the last of these
    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

  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,822
    =DLookUp("item_price", "Price Table", "[Price Table]![item_code]=" & [code] & " And [Price Table]![item_size]=" & Switch([Qty]<=3, 10, [Qty]<=10, 20, True, 30))

    Are you doing this in textbox on form?
    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
    pcc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    5
    Thanks.
    i also want to use item price to calculate the total and subtotal. but it is returning the error while code on form is null

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maybe wrap in Nz() function.

    Nz([code],0)
    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
    pcc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    5
    thanks for help . fixed the price field issue by warping up in iif([code]=0,0,.... and it is working is total as

    =[QTY]*[Price] but unable to calculate the sum of in the form. its is returning #Error.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You were given several solutions
    So we're not guessing, please post the latest version of the formula you are using
    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

  9. #9
    pcc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    5
    =iif([code]=0,0,(DLookUp("item_price", "Price Table", "[Price Table]![item_code]=" & [code] & " And [Price Table]![item_size]=" & Switch([Qty]<=3, 10, [Qty]<=10, 20, True, 30)))

    Quote Originally Posted by ridders52 View Post
    You were given several solutions
    So we're not guessing, please post the latest version of the formula you are using

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Aggregate functions must reference field in RecordSource, not controls on form. So if Price is the textbox with the DLookup() the Sum() will fail. And even if Price were field name, if Sum() just references the textbox with =[Qty]*[Price] calc, it will still fail.
    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.

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
    By pcandeias0 in forum Programming
    Replies: 3
    Last Post: 07-09-2011, 02:31 PM
  4. DLookUp Help
    By ThaGreenMoose in forum Forms
    Replies: 10
    Last Post: 06-15-2011, 12:08 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