how do i display name using effective date using M Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, im trying to list the staff strength for that dates entered by the user
using the criteria : between [Date 1] AND [Date 2]. I have a table name
Personal_Data which has DateJoined field and DateTernimate field.

For example,
EmployeeID DateJoined DateTerminated Status
1000 1 January 2005 1 May 2005
Terminated
1001 2 February 2005 5 March 2005 Active

If User input from the prompt is between 23 January 2005 to 3 March 2005,
how do i code the program to capture active users between that period. Please
help cos i've been trying to solve it for a day!!

Email to me at: (e-mail address removed)
 
Have you tried this?

Select EmployeeID, DateJoined, DateTerminated, Status From Personal_Data
Where DateJoined < [Date 2] and DateTernimate > [Date 1]
 
Hi,


To know if there is no overlap between two intervals: [starting, finishing]
and [start, finish], use the criteria:


finish < starting OR start > finishing


since in that case, there cannot be any "collision".

To get cases when there is any kind of collision, negate the statement
(using DeMorgan's law):


finish >= starting AND start <= finishing


So:

DateTerminated >= paramStart AND DateJoined <=
paramFinish


will keep record where an employeID was "active", given the [paramStart,
paramFinish] interval.



Hoping it may help,
Vanderghast, Access MVP
 
yeah, it works. thanks many bunchies!!

Ofer said:
Have you tried this?

Select EmployeeID, DateJoined, DateTerminated, Status From Personal_Data
Where DateJoined < [Date 2] and DateTernimate > [Date 1]

--
I hope that helped
Good luck


LizA said:
Hi, im trying to list the staff strength for that dates entered by the user
using the criteria : between [Date 1] AND [Date 2]. I have a table name
Personal_Data which has DateJoined field and DateTernimate field.

For example,
EmployeeID DateJoined DateTerminated Status
1000 1 January 2005 1 May 2005
Terminated
1001 2 February 2005 5 March 2005 Active

If User input from the prompt is between 23 January 2005 to 3 March 2005,
how do i code the program to capture active users between that period. Please
help cos i've been trying to solve it for a day!!

Email to me at: (e-mail address removed)
Click to expand...
Click to expand...
 
Back
Top