Select from range of dates 1

[razchip]

I just can't seem to find the answer that should be fairly easy. I have a date in one Production table and I need to find out which period it falls in from my Accounting period table. An example from the production table would be 2/23/2017 and I need to select the proper period from the following:
ActgIndx startperiod end period
1 12/26/2016 1/31/2017
2 2/1/2017 2/28/2017
3 3/1/2017 3/27/2017

The result for 2/23/2017 should be 2.
I've tried numerous things, but keep drawing a blank.
Appreciate any direction, thanks.

Thanks for the help.
Greg

 

[combo]

Use two tables in design view, without any linking, required production date within any of existing accounting periods:
[pre]SELECT
ProductionDate, ActgIndx
FROM
Production, AccountingPeriod
WHERE
[ProductionDate] Between [StartPeriod] And [EndPeriod][/pre]

combo

 

[Andrzejek]

You may also try:

[pre]
Select ActgIndx From AccountingPeriod
Where (Select ProductionDate From Production
Where <condition>)
Between StartPeriod And EndPeriod[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[razchip]

Thank you, I ran them both, and each one worked. Made my day a lot better.

Thanks for the help.
Greg

 

[dhookom]

razchip,

Give some kudos by clicking the "Great post!" link. This lets us know the thread is closed successfully and honors the helpers.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016