Search code examples
ms-accessvbabetweeniif

Look up a date in a table with a list of multiple start and dates and return a default value if found

I am having some trouble trying to create a query that will return a default value if the date being looked up falls between any of the start and end times in another table.

The date being looked up is in table1 and start and end dates are in table2. I need for the value in table 1 to be compared to each of the start and end dates in table2.

So what i would like is if [table1].[date] is between any of the list of [table2].[startdate]'s and [table2].[enddate]'s then the value "TRUE" to be shown, otherwise [table1].[value] to show.

Hopefully this makes sense.

Solution

  • That could be:

    Select 
    [table1].[date],
    IIf([table1].[date] Between [table2].[startdate] And [table2].[enddate], "TRUE", [table1].[date]) As DateFound
From
    [table1],
    [table2]