Results 1 to 10 of 10
  1. #1
    mattvogt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Location
    Denver Co
    Posts
    5

    dlookup value within a range on separate table

    I want to use [LevelOfService] which can be any number, find where it lies within the range of [MinMbps] and [MaxMbps], and derive the [BookPrice]



    I am using two tables [tblOrders] and [tblPriceBook].
    [tblOrders] has the field [LevelOfService]
    [tblBookRate] has the fields [MinMbps], [MaxMbps], and [BookPrice].

    I have attempted to use the following:
    Code:
    dlookup("[BookRate]","[tblPriceBook]", "[tblPriceBook].[MinMbps]< & [tblOrders].[LevelOfService] & AND [tblPriceBook].[MaxMbps]>")
    I am attempting this in a query.
    Any input is appreciated. I’m quite new at this and don’t have much experience in the realm of Access.

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Try

    Code:
    dlookup("[BookRate]","[tblPriceBook]", "[tblPriceBook].[MinMbps]< [tblOrders].[LevelOfService] AND [tblOrders].[LevelOfService] < [tblPriceBook].[MaxMbps]")

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you attempting this in a query or textbox on form? Regardless, need to concatenate the LevelOfService to pull value from current record.

    Is the field BookRate or BookPrice? Is the table tblPriceBook or tblBookRate? See the contradictory info in original post.

    DLookup("[BookRate]", "[tblPriceBook]", [LevelOfService] & " BETWEEN [MinMbps] AND [MaxMbps]")
    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
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by June7 View Post
    Are you attempting this in a query or textbox on form?

    DLookup("[BookRate]", "[tblPriceBook]", [LevelOfService] & " BETWEEN [MinMbps] AND [MaxMbps]")
    FWIW, June7's version is essentially a LevelOfService >= MinMbps AND LevelOfService <= MaxMbps. If you want to include a match of LevelOfService to the MinMbps and MaxMbps then Between will work just fine. But if you don't want them to be a match then you need to use the syntax I provided.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, but still need the concatenation.

    DLookup("[BookRate]", "[tblPriceBook]", [LevelOfService] & ">[MinMbps] AND " & [LevelOfService] & "<[MaxMbps]")

    True, BETWEEN AND is inclusive for the range ends and simpler form of >= AND <=
    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.

  6. #6
    mattvogt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Location
    Denver Co
    Posts
    5
    Thanks for pointing out the contradictions in my original post, I have corrected them and noted that this is for a query. With that, I have put the expression in the query Field: and get the error that 'Microsoft Access cannot find the name 'tblOrders.LevelOfService' you entered in the expression'. My [tblOrders] certainly contains a [LevelOfService] field, and it is all spelled correctly.

    Thank you all for your input, it's much appreciated.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This query is based on tblOrders? Try without the tblOrders prefix.
    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.

  8. #8
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by June7 View Post
    This query is based on tblOrders? Try without the tblOrders prefix.
    Yeah, I realize my mistake in there too.

    For values that are between but not equal to MinMbps and MaxMbps....
    Code:
    DLookup("[BookRate]", "[tblPriceBook]", [LevelOfService] & ">[MinMbps] AND " & [LevelOfService] & "<[MaxMbps]")
    


    For values that are between or equal to MinMbps and MaxMbps then...
    Code:
    DLookup("[BookRate]", "[tblPriceBook]", [LevelOfService] & " BETWEEN [MinMbps] AND [MaxMbps]")
    

  9. #9
    ehvezda is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    1
    mattvogt, how are you relating the tables? I'm trying to do something similar, but can't seem to get the tables related correctly.
    Thanks

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    @ehvezda, bumping very old thread and hijacking a thread are considered poor forum etiquette. Likely @mattvogt is not following this thread and will never read your post much less respond to it. If you need help from forum, start your own thread. Reference another thread if you think it can be helpful in understanding your issue.
    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. Can't separate project data based on date range
    By jason.cyr13 in forum Queries
    Replies: 1
    Last Post: 01-29-2014, 11:32 AM
  2. Default value from separate table
    By dgtampa in forum Forms
    Replies: 2
    Last Post: 10-31-2011, 07:41 PM
  3. New user - separate a table into two tables
    By Henry_Reimer in forum Database Design
    Replies: 19
    Last Post: 10-08-2011, 10:19 PM
  4. Dlookup to find value in a range
    By jobrien4 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 11:00 AM
  5. Replies: 1
    Last Post: 06-17-2011, 12:59 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