VBA query code to select a date between a start and finished date (1 Viewer)

pekajo

Registered User.
Local time
Tomorrow, 07:55
Joined
Jul 25, 2011
Messages
133
Hi,
Hope this is not to complicated. I have a [startdate] and a [finishdate] fields and want to show all the people who will be working on a [selecteddate] (variable).

[startdate] = #01/03/2024#
[finishdate] #/01/05/2024#
For everyone who will be at work between these dates and will be at working on the #10/03/2024#
So if a person leaves on the #08/03/2024# will not appear on the query.
Cheers
Peter
 
Last edited:

June7

AWF VIP
Local time
Today, 13:55
Joined
Mar 9, 2014
Messages
5,497
Your dates are dd/mm/yyyy?

Provide a sample dataset. You can build a table in post.
 

GaP42

Active member
Local time
Tomorrow, 07:55
Joined
Apr 27, 2020
Messages
359
Query is like

Code:
SELECT Empname, EmpStartDate, EmpFinishDate
FROM qryEmpPeriod
WHERE EmpStartDate  < StartDate AND EmpFinishDate > strSelectDate

Note that if the employee starts after the StartDate and before the SelectDate and works at least till after the SelectDate they are not included. Is that what you want?
If they are to be included then you test
Code:
WHERE EmpStartDate < strSelectDate and EmpFinishDate > strSelectDate

where you establish in your vba that strSelectDate = "#" & SelectDate & "#"
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:55
Joined
Sep 21, 2011
Messages
14,473
Ok, I will bite, why cannot you just test for those working on 10th March?
What happens if I am employed there, but have that day off?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:55
Joined
Feb 19, 2002
Messages
43,513
@pekajo You never responded to the question about your date format. So, before this thread spirals down the rabbit hole, let us clarify that.

[startdate] = #01/03/2024#
[finishdate] #/01/05/2024#

String dates - which these ARE, MUST be in mm/dd/yyyy or yyyy/mm/dd field order. If these are in dd/mm/yyyy order, the data base engine will ASSUME that all ambiguous dates are mm/dd/yyyy and NOT dd/mm/yyyy regardless of your regional settings

So, reformat your STRING dates to yyyy/mm/dd so you avoid personal confusion

If your query references the form fields directly, you won't have to format anything.

Select .... From ....
Where YourDate Between Forms!yourform!StartDate and Forms!yourform!FinishDate

will work fine as a querydef.
 

Users who are viewing this thread

Top Bottom