Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Question Unanswered: DLOOKUP to display Value = Smallest (earliest) Autonumber

    I'm trying to use DLOOKUP in a subreport to return a Start Balance or in other words the "Balance" value of a query that matches lowest (earliest) "Transaction Number". The subreport uses a query that looks like this:
    Date Balance Tran Number
    1/7/08 12.35 123
    1/7/08 14.97 124
    2/7/08 35.96 125
    2/7/08 76.98 126
    2/7/08 98.76 127

    It uses the same date criterior as the main report so the start balance is simply the earliest entry and the end balance is the most recent. Trans Number 122 isn't shown for example because it occurs before the 1st July and is outside the critrior of the main report. So in the above example Start Balance = Tran Number "123" = 12.35.

    My problem is I've no idea how to get DLOOKUP to do this. My guess would be something like:
    =DLookUp("[Balance]","[BALANCE]","[TRANNUMBER] < [TRANNUMBER]"

    Any help?
    ----------------
    Now playing: Jimmy Page & Robert Plant - That's the Way
    via FoxyTunes

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Had a look at DMin instead of DLookup?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2007
    Posts
    40
    DMIN - and by extension DMAX. I've never come accross them before. Exactly what I wanted. Thank you StarTrekker

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome

    There is also a DAvg, DFirst, DLast, DSum and a DCount
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •