Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1

    Answered: DLookup for nearest value

    Hi everyone,

    I'm looking for a way to replicate a VLookup (with True match setting) in Access VBA, in the sense of a DLookup which if it doesn't find a perfect match, gives the nearest value below the searched value. I have a table of peoples' ages (for the sake of argument (Age_p), and a lookup table of rates (Rates) dependent on 3 fixes ages (Age).

    I've tried DLookup("LookupField", "Table", "Criteria")

    With Criteria such as Age <= Age_p, Age Between 0 and Age__p and several things, but nothing's worked so far - I keep getting errors or nonsensical results.

    For the example values below, I'm looking for something which'll give me 0.05% for any Age_p between 18 and 49.99, 0.07% for Age_p between 50 and 59.99 and 0.01% for any Age_p above 60%.

    Click image for larger version. 

Name:	Rates.JPG 
Views:	1 
Size:	10.5 KB 
ID:	17217Click image for larger version. 

Name:	Rates.JPG 
Views:	1 
Size:	10.5 KB 
ID:	17217

    Thanks in advance

  2. Best Answer
    Posted by raphael_b

    "So I've got the formula working, although it's really dumb and I don't know if it's the right way of doing it.. apparently the DMax and DLookup can't compare an integer with a Double.. I only got the formula working by using Int on Age_p. In the end, Age <= Age_p was the right criteria to use.. sorry for the bother to anyone who tried to work this out for me and hasn't posted yet."


  3. #2
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1
    So I've got the formula working, although it's really dumb and I don't know if it's the right way of doing it.. apparently the DMax and DLookup can't compare an integer with a Double.. I only got the formula working by using Int on Age_p. In the end, Age <= Age_p was the right criteria to use.. sorry for the bother to anyone who tried to work this out for me and hasn't posted yet.
    Last edited by raphael_b; 12-05-16 at 04:11.

Posting Permissions

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