Mapping data via join and Nz() for dates

[Fuii]

Hi folks,



I've got an issue I did solve couple of years ago already, but I can't remember how - the curse of doing it only once.

I've got 2 tables.

Table 1 - history - contains an integer and a date field. Duplicates allowed.
Table 2 - Map - contains an integer, 2 date fields and an additional field that is to be added in the query. Duplicates allowed.

Both tables are joined via the integer fields:




The intention is to get all lines from "history" and add the "DataToAdd" column from "Map" to it, though both tables contain duplicates for the join clause.
To get the correct one, the History.DateClosed must be inbetween of Map.DateStart and Map.DateEnd.
Additionally, Map.DateEnd can be empty/null - I guess using Nz() here with Now() as null clause.

My issue is, that I can't figure out the proper filtering in combination with Nz().

Here are some samples and the intended output:




Thanks in advance.

[pbaldy]

I assume what you're hung up on is the join. In addition to the join on job/employee, you want a non-equi join on the date fields. That can't be represented in the design grid, so you'll have to do it in SQL view. It would look like:

...And DateField >= FromDate And DateField <= ToDate

I'm on a mobile so hard to type but hopefully that points you in the right direction.

[Fuii]

Thanks for your reply. I did extend the Join via SQL as suggested, but the query returned no results.

Query:

SELECT History.JobID, History.DateClosed, Map.DataToAdd
FROM History 
INNER JOIN Map ON History.JobID = Map.EmpID 
    and History.DateClosed >= Map.DateStart
    and History.DateClosed <= Map.DateEnd

[pbaldy]

You haven't included the Nz(). I don't know if it will work in the join, you may need to use a base query that applies it. If that doesn't help, can you attach a sample of the db here?

[CJ_London]

have you tried

SELECT History.JobID, History.DateClosed, Map.DataToAdd
FROM History INNER JOIN Map ON History.JobID = Map.EmpID
WHERE History.DateClosed >= Map.DateStart     and History.DateClosed <= nz(Map.DateEnd,History.DateClosed)

you mention using the now function - that returns date and time. To return just the date, use the date function

[Fuii]

Thanks for our assistance so far guys.

Using Ajax' query or the adjusted from pbaldy with included Nz() yielding the same result:



The rows with ID 102 that have an end date of 07/31/2020 are missing. :/

Attached is the sample DB including your both queries.

you mention using the now function - that returns date and time. To return just the date, use the date function

Due to working with Date/Time fields in queries and extract dates or times from them, I'm used to do Int(Now()) in that case - which gets the current date too - old habit.

[pbaldy]

I think my query works (probably Ajax's too), the problem is the DateEnd value of 7/31/202. Note 202, not 2020.

[Fuii]

hmm, *blush*
I think I'll just sink into the ground, in a dark corner.

Indeed, after correcting the year both queries work as intended.

[pbaldy]

LOL! No problem, we've all done it.