Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Simple Dlookup question

    I am trying to retreive information from a table using the dlookup command. The problem is I want to return the value from one row higher than the one the dlookup is returning. Any ideas? (The table contains only text and I would rather not use an autonumber)

    Thanks!

    Norm

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I don't think that's Dlookup. I would seek the value, return the absolute position, move first, then move the abs pos -1
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Simple Dlookup question

    Originally posted by norm801
    I am trying to retreive information from a table using the dlookup command. The problem is I want to return the value from one row higher than the one the dlookup is returning. Any ideas? (The table contains only text and I would rather not use an autonumber)

    Thanks!

    Norm
    Hi Norm...

    There's got to be something... be it a date or number or something unique that tells you which record you want... You said "one row higher"... but what if someone sorts the table data in a different way??... Without some unique identifier, you'll be up a creek without a paddle... ... lol... Soooo... I'm going to assume you've got something that explains what record you really want... Whether it's Max(fldDate) - 1 or something like that...

    Let's see the DLookup statment...

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Instead of doing that one above, why dont u list ur criteria for finding this elusive row?

  5. #5
    Join Date
    Jan 2004
    Posts
    100

    Re: Simple Dlookup question

    Originally posted by Trudi
    Hi Norm...

    There's got to be something... be it a date or number or something unique that tells you which record you want... You said "one row higher"... but what if someone sorts the table data in a different way??... Without some unique identifier, you'll be up a creek without a paddle... ... lol... Soooo... I'm going to assume you've got something that explains what record you really want... Whether it's Max(fldDate) - 1 or something like that...

    Let's see the DLookup statment...
    Ok, here's the scenario:

    Table:

    FiscalMonthStart..........FiscalMonthEnd
    10/26/2003.................11/29/2003
    11/30/2003.................12/27/2003
    12/28/2003.................1/24/2004
    1/25/2004...................2/28/2004
    2/29/2004...................3/27/2004
    3/28/2004...................4/24/2004
    4/25/2004...................5/29/2004
    5/30/2004...................6/26/2004
    6/27/2004...................7/24/2004

    I am trying to reference the period prior to where today would fall. Today is 2/9/04 (which is between 1/25/04 and 2/28/04) and I would like to have it call up 12/28/2003 for the FiscalMonthStart and 1/24/2004 for the FiscalMonthEnd.

    Norm

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Simple Dlookup question

    Originally posted by norm801
    Ok, here's the scenario:

    Table:

    FiscalMonthStart..........FiscalMonthEnd
    10/26/2003.................11/29/2003
    11/30/2003.................12/27/2003
    12/28/2003.................1/24/2004
    1/25/2004...................2/28/2004
    2/29/2004...................3/27/2004
    3/28/2004...................4/24/2004
    4/25/2004...................5/29/2004
    5/30/2004...................6/26/2004
    6/27/2004...................7/24/2004

    I am trying to reference the period prior to where today would fall. Today is 2/9/04 (which is between 1/25/04 and 2/28/04) and I would like to have it call up 12/28/2003 for the FiscalMonthStart and 1/24/2004 for the FiscalMonthEnd.

    Norm
    Just what I thought... k... Let's think this through... Looks like you want the Maximum record where the FiscalMonthEnd field is less than today's date...

    Try using...
    DMax("[FiscalMonthStart]", "YourTableName", "[FiscalMonthEnd] < #" & Date() & "#")

    HTH

  7. #7
    Join Date
    Jan 2004
    Posts
    100

    Re: Simple Dlookup question

    Originally posted by Trudi
    Just what I thought... k... Let's think this through... Looks like you want the Maximum record where the FiscalMonthEnd field is less than today's date...

    Try using...
    DMax("[FiscalMonthStart]", "YourTableName", "[FiscalMonthEnd] < #" & Date() & "#")

    HTH
    So simple yet elusive! I finally got it to work by adding an extra column with numbers and then have a textbox with the number in it using a vlookup to refer to that textbox -1.......your solution is much easier!

    Thanks.

    Norm

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Isn't adding a column with numbers, the autonumber solution you didn't want to use?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by HomerBoo
    Isn't adding a column with numbers, the autonumber solution you didn't want to use?
    I implemented the autonumber solution (which I will now undo) prior to getting a more efficient solution from the forum.

  10. #10
    Join Date
    Dec 2002
    Posts
    117

    At least now

    It works!

Posting Permissions

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