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

[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?

 

[Pat Hartman]

@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.

 

[cheekybuddha]

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