Bring Back Job Position When Date Between Two Dates - MS Access Query

HI - I have two queries:

Query 1 - Has employee number and effective date

Query 2 - Has Begin and End Dates and Job Position

What Access function can I build to bring the Job Position together with the "effective date" in Query 1?

An example spreadsheet is attached..  Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I believe that this DLookup will give you what you need
Expr1: DLookUp("Position";"Query2";"Beginning <= #" & [Date] & "# AND END >=#" & [Date] & "# AND Employee = " & [EmployeeID])

Open in new window

You should avoid the FieldName Date in Query1 because it will definitely cause issues
Rey Obrero (Capricorn1)Commented:
try this query

SELECT Q1.EmployeeID, Q1.Date AS [Effective Date], Q2.Beginning, Q2.End, Q2.Position
FROM Q1 INNER JOIN Q2 ON Q1.EmployeeID = Q2.Employee
WHERE (((Q1.Date) Between [Q2].[Beginning] And [Q2].[End]));

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Using domain functions in queries or code loops is extremely expensive.  Each row in the resultset of the query or each iteration of the loop in code will cause a separate query to run for the domain function.  So, if you have a thousand records, you are running a thousand queries.  The join specified by Rey allows the database engine to do the heavy lifting and it is significantly more efficient.  If you never work with any recordset larger than a few hundred rows, you can get away with it but beyond that, I would not recommend it.
tomfarrarAuthor Commented:
Thanks for all the comments.  John, I was not able to get your DLookUp to work for me, and with further feedback from Pat thought it better to go with Rey's query (even though I technically ask for a Access function.  Rey's query gave me the result I was hoping for.  Thanks again to all of you.  - Tom
tomfarrarAuthor Commented:
Thanks, Rey.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.