Avatar of Tom Farrar
Tom FarrarFlag for United States of America

asked on 

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.
Microsoft Access

Avatar of undefined
Last Comment
Tom Farrar
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of PatHartman
Flag of United States of America image

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.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image


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
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image


Thanks, Rey.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo