Query DATES Issue

[rmason]

Please help with this problem, it;s kicking my butt!!
I have a parameter query that prompts for the following fields:

Unit Startdate enddate

This is supposed to pull any employees who has worked on a particular unit during the time frame specified by the user. Here is the problem: If I query with the following parms:

Unit = ESD StartDate = 01/01/03 Enddate = 07/01/03

The above will return: Bob Smith ESD 02/05/03 06/01/03
So far so good.... IF i use the following parms:
Unit ESD StartDate 04/01/03 Enddate 04/30/03
I get 0 records back(??) As you can see I should have still pulled Bob Smith because my range was April and he was here from Feb. to June(??) Can anyone help me with this...please?
FYI: I have tried using Between startdate and enddate
>=Startdate and <=Enddate
I will attach a sample of my DB

 

[dcx693]

The problem is your logic, I think.

Bob was there between Feb and June, so he is entirely contained within the range of January thru July. That's why the first set of dates works. However, he is not entirely contained within April.

You need to decide how you want Access to pull records: (1) when the person has any part of his range contained within the search range or (2) when the person is entirely contained in the search range.

Right now, I think you're doing (2). For (1), you need to specify that the person's start date is anywhere within the search range OR his end date is anywhere within the range.

 

[rmason]

Can you please give me an example of what I need to do? I am sort of a novice at this, please ba very basic. Thanks again!!

 

[dcx693]

I think you need two criteria. Something like: (1) [person_start_date] Between [StartDate] And [EndDate] and also (2) [person_end_date] Between [StartDate] And [EndDate].

Those should be on separate lines in your query grid. That should pick out when any user's range is within your date range.

 

[rmason]

Sorry DCX, It's still not pulling any records. Any other ideas? I cannot believe this is what is kicking my butt.

 

[namliam]

PersonStartDate <= ProjEndDate and
PersonEndDate >= ProjStartDate

Regards

the Mailman

 

[rmason]

Thanks Guys! I am almost there. One more thing pertaining to this. How do I handle if I am seraching for people and some of the employees have no ending date because they are still employees? Example


Employee Unit Start End
Smith ESD 01/01/03


I run my query to see who worked in ESD from Feb1. 01 to Feb.28, 01
Since Smith Has no ending date, How can I adjust the query selection to handle the null end dates? Thanks again guys!!!

 

[dcx693]

namliam's brain is working, whereas mine still needs to wake up. His solution is correct, it just needs a tweak to work with Null end dates. I came up with a simple one. Take a look at the attached jpg.

 

[rmason]

Apparently my brain is not working either, What is the modifiedEnd Field? Sorry for the lack of knowledge. Here is a screenshot of the current query that works other than the null issue. Thanks

 

[dcx693]

rmason, the "ModifiedEnd" field is a calculated column. We needed to handle the case where the End date for the employee could be Null. The ModifiedEnd field uses the Nz() function to modify the value of the End field so that if the field value is Null, it gets changed to being the same date as the one the user specified when prompted by [Enter start date:]. That way, if Null, the end date will always become equal to the start date the user entered. Simple, huh?

 

[namliam]

(PersonStartDate <= ProjEndDate
or ProjEndDate is Null)
and
(PersonEndDate >= ProjStartDate
or PersonEndDate is Null)

I also worked in: What if there is no project enddate yet?

Regards

The "working brain" Mailman

 

[rmason]

Yes, Clear as Mud

 

[namliam]

DCX your solution will work however its not prevered. As in the end result you will be showing a date which should not be there.

Halfway solution would be to do this in a field your NOT showing in the end result (to the user), but then still... Null values are there for a reason! Use them!

Regards

 

[rmason]

This is correct, the null values are there for a reason and cannot be populated with an inaccurate date. Another Example



Bob Jones ESD 02/01/01 05/06/01

Sue Smith ESD 03/10/01

If I run a query to show me who worked in the ESD from 04/01/01 - 04/30/01 it needs to give me both of the above employees because Bob worked there in April and left, Sue worked there in April, 01 and is still there. See what I need to do? Thanks

 

[namliam]

Yes i see....

I also see a previous post of mine you didnt see....

It has the solution you need!!!!!!!

(PersonStartDate <= ProjEndDate
or ProjEndDate is Null)
and
(PersonEndDate >= ProjStartDate
or PersonEndDate is Null)

I also worked in: What if there is no project enddate yet?

Regards

The "working brain" Mailman

REGARDS

 

[rmason]

Thanks Namliam, I did not see prior post. I will try this and hopfully get this monkey off my back. R

 

[namliam]

Good luck

Regards

The "slightly P-O-ed" Mailman

 

[rmason]

Mailman...Guess who.........Sorry, you can give up on me and i will understand : )
I changed the query to reflect your last post, What it gives me is ALL Employees who have a NULL start date and or NULL end date due to the "Or Date isNull" I only need to see the null values for the user specified date range for a particular unit. I did not think this would be the part that would do me in!!!! Thanks again for everyones help. Rick