date confusion

[UGAJohn]

I would like to run a query to determine if a datetime field in table1 falls
between start date and end date in table2. If so I would then like to
assign those dates an ID based on a field in table2. How does one do this?

 

[Jeff Boyce]

I'm trying to imagine/envision a query in design view.

Both tables would be in the upper portion.

The table1 datetime field would be added to the fields to display in the
lower portion.

Under it, in the selection criterion, there'd be something like:

Between [Table2Name]![StartDate] And [Table2Name]![EndDate]

I'm not sure I get the "assign an ID from table2", though.

More info?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[cm]

might need more information, but here's a guess:

(Assuming a table join on field called empno in each table)

select table1.*, table2.ID

from table1, table2

where table1.empno = table2.empno
and table1.date between table2.date1 and table2.date2