compare date field in 1 table to start & end date fields in 2nd ta

[Guest]

I have 1 table that lists schools with their holiday dates (among other info)
and in a 2nd table I have students with corresponding date ranges in 2 fields
- from_date and to_date to indicate their attendance. I need to find any
records in the student attendance table where the holiday dates in the 1st
table are within the range of the from and to dates. Can anyone help?

 

[Guest]

Try this ---
SELECT [1st_Table].School, [2nd_Table].Student, [1st_Table].[holiday dates]
FROM 1st_Table, 2nd_Table
WHERE ((([1st_Table].[holiday dates]) Between [from_date] And [to_date]));

 

[John W. Vinson]

I have 1 table that lists schools with their holiday dates (among other info)
and in a 2nd table I have students with corresponding date ranges in 2 fields
- from_date and to_date to indicate their attendance. I need to find any
records in the student attendance table where the holiday dates in the 1st
table are within the range of the from and to dates. Can anyone help?

A "Non Equi Join" query is one way to do this. It's a bit obscure but Access
does support it!

You need to get into the SQL window to do it. If you're not comfortable with
SQL, first create a query joining the holidays table to the From_Date field,
and selecting any fields that you want to see. Then select View... SQL on the
menu, or use the SQL icon on the dropdown tool on the left end of the query
design toolbar. Edit the SQL text to replace

INNER JOIN Holidays ON Holidays.Datefield = yourtable.From_Date

to

INNER JOIN Holidays ON Holidays.DateField >= yourtable.From_Date AND
Holidays.DateField <= yourtable.To_Date


John W. Vinson [MVP]