VBA query code to select a date between a start and finished date

[pekajo]

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

 

[June7]

Your dates are dd/mm/yyyy?

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

 

[GaP42]

Query is like

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

WHERE EmpStartDate < strSelectDate and EmpFinishDate > strSelectDate

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

 

[Gasman]

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?

 

[cheekybuddha]

Why does [finishdate] have a leading / (slash) ?