Question on dates

[scschuck]

This is my first post, so hopefully I follow protocol. I have a database of roughly 8000 employees of the the course of 10 years. I know we can pull start dates, end dates no problem. My questions is this...How can I type in a date and know who is working for me or was working for me at any given date. IE...I want to know for July 4, 2001, who was with me...Or from May 15, 2016 through May 17, 2016, who was with us...



I would appreciate any and all wisdom on this.

[Micron]

Compare the EndDate to your supplied date. If it's less than or equal to, they were there then. If you include Null, they are still there because there is no end date (or the entry was forgotten). So you could have separate query fields with the necessary calculation, or put EndDate <= [Enter a Date] on one criteria row and Is Null on the next criteria row for the same field. The words between those square brackets will provide a prompt for the date.

[John_G]

Welcome to the forum.

I'll start you off with the logic. For the first case, where you have only one test date (call it Date1), you want to find all employees who started before that date, and who finished after that date or are still with you:

[StartDate] < [Date1] AND ( [EndDate] > [Date1] OR [EndDate] is Null )


If you have a date range Date1 and Date2, it's only slightly different. You need employees who started somewhere in the date range, or who started before the first date and ended after the first date or are still with you:

( [StartDate] between [Date1] and [Date2]) OR ( [StartDate] < [Date1] AND ( [EndDate] > [Date1] OR [EndDate] is Null ) )

Actually, you only need the second one, because the first scenario - with only Date1 - is a special case of the second scenario, but with Date2 = Date1.

How you use that (i.e. syntax etc.) will depend on whether you are using a query or a form with SQL. Working with dates can be "tricky" to say the least.

Post back if you need help with how to use the logic.