Find a value in a table based on a date

Based on a date, I must search a table for a value from another column, but the date is not necessarily exact, it can be within a range or be greater than the last one in the table.

For example

VUT - Table

|Since |Value|
| 19/02/2014 | 127 |
| 25/02/2015 | 150 |
| 11/02/2016 | 177 |
| 25/02/2017 | 300 |

Depending on the date of a transaction, I must take the value of the “Value” field and perform a calculation, but the date of the transaction can be anyone and not necessarily the one specified in the table, each record establishes a range and must take the "Value "depending on where you are

I would appreciate your comments.

What is that calculation?


Then how do you know what date to use?


This makes no sense at all.

The problem you’re trying to solve is entirely unclear.

1 Like


I’ve to search in the table with a date variable.

the date must be greater than any … but it must not be greater than the next … if it exists

If I look for the date 30/06/2016 the result should be 177

If I search 30/3/2017 the result should be 300

if i search a date before the first Since Date Value … it have to give an error

The dates establishes the limit of a range of dates … and Value is the result expected

Thanks for your time

1 Like

So, given a date, find the table row with the closest lower date and return the value for that row?

      = MIN(
          ([Since] <= [_THISROW].[Date])

Something likes … i will try with your code … thanks a lot

1 Like

Have to change MIX to MAX on the SELECT … the list for this SELECT takes the rows where Dates ar greater and equal to Since … but the closest Since is the MAX

1 Like