Date fields in queries

[indyaries]

Greetings,

I have two tables. One has Employee info (Names), one has the Start and End dates of any Developmental Assignments (D.A.) that they have been on.

The date fields are called Start_Date and End_Date, format is ShortDate.

Example: Joe Goodemployee is placed on a D.A. on 10/05/01 (05 Oct 2001). It lasts until 02/10/02 (10 Feb 2002). Joe Goodemployee would need to show up 5 times...when a report is run for all D.A. employees in Oct 2001, he should show up. Same with Nov - Dec - Jan - Feb.

Just getting employee's to appear if they had a D.A. during the month being queried (I'm guessing a parameter of some type-asking the month to query) would be great.

Thanks if Advance -- this is driving me bonkers !!

Bob in Indy
aries170@comcast.net

 

[boblarson]

In the criteria of the Start_Date, put: >=[Enter Start Date]

And in the End_Date criteria put: <=[Enter End Date]

That will prompt the user for the beginning and ending dates (inclusive) and you will get only those records between those dates.

BL
hth

 

[indyaries]

Thank you for replying. Yes, I was aware of the use of this particular Parameter query. However, it still does not yield the info I need.

Here is another example of my tables. As you can see, an employees Developmental Assignment (D.A.) can span more than one month.

Table1 = Employee Names (KeyField = EmployeeID)
Table2 = Developmental Assignment info, including Start_Date and End_Date.

FirstName Start_Date End_Date StartMonth EndMonth WorkDays
Larry 12/9/01 12/21/01 Dec 2001 Dec 2001 9
Mary 12/1/01 12/31/01 Dec 2001 Dec 2001 20
Delphine 1/14/02 1/18/02 Jan 2002 Jan 2002 4
Karen 1/14/02 1/18/02 Jan 2002 Jan 2002 4
Mindaugas 1/14/02 1/18/02 Jan 2002 Jan 2002 4
Pauline 10/28/01 1/27/02 Oct 2001 Jan 2002 64
Anthony 2/8/02 2/22/02 Feb 2002 Feb 2002 10

I have to provide, in a report;
By month, the name(s) of any employee in D.A.

Example: If the user wants to see all employees in a D.A. in December 2001;

RESULTS:
START END
Larry 12-09-01 12-21-01
Mary 12-01-01 12-31-01
Pauline 10-28-01 01-27-02

Larry and Mary were both in a D.A. in Dec 2001, but Pauline was also in a D.A. during that time period; she just happened to begin in Oct 2001, and ends in Jan 2002.

I'm guessing I need something like this:
If (Date I'm querying on) is greater than-equal toStart Date AND less than-equal to End Date, display names and dates.

Again, Thanks for any help you can render.

Sincerely,

Bob



[This message has been edited by indyaries (edited 04-30-2002).]

 

[RichMorrison]

Bob,

It sounds like you want something like this:

SELECT StartDate, EndDate, #4/30/02# AS Expr1
FROM tblDate
WHERE (((#4/30/02#) Between [StartDate] And [EndDate]));

You just have to get your report date, 4/30/2202 is this example, into a query.

RichM

 

[indyaries]

Rich, thank you for your reply. Here is the SQL from the query. I've tried placing what you provided into this, but it returns errors. Am I to place the SQL you provided into my SQL, or into the query grid someplace?

Thanks in advance !!!

SELECT tbl_DNO_Employees.EmployeeID, tbl_DNO_Employees.LastName, tbl_DNO_Employees.FirstName, DevelopmentalAssignments.Start_Date, DevelopmentalAssignments.End_Date, DevelopmentalAssignments.Location, DevelopmentalAssignments.Remarks
FROM tbl_DNO_Employees LEFT JOIN DevelopmentalAssignments ON tbl_DNO_Employees.EmployeeID = DevelopmentalAssignments.EmployeeID
WHERE (((DevelopmentalAssignments.Start_Date) Is Not Null))
ORDER BY DevelopmentalAssignments.End_Date, tbl_DNO_Employees.LastName;

 

[indyaries]

Greetings All,

I received the below reply from mztaib on the ezboard users group; http://pub59.ezboard.com/fmsaccesshelpguidefrm1.showMessage?topicID=460.topic

Anyway, this is exactly what I needed, and it works great. Thought I would post his answer here in case it can help anyone else. Thanking everyone who responded to my post.


Re: Query or Report Question-Dates
--------------------------------------------------------------------------------
Hi Bob,

1. Make a query
2. Set these criteria
3. In the criteria row, for Start_Date column
enter this criteria, Between [DateFrom] And [DateTo]
4. In the next row, i.e. the 'or' row
for Start_Date column, enter, <[DateFrom]
for End_Date column, enter, >[DateFrom]

When you run this query, you'll be prompted with DateFrom and DateTo. So, if you want to know D.A. in Dec 2001, enter DateFrom = 12/1/2001 and DateTo = 12/31/2001, i.e. the beginning and the end date of the month. The query will show records when the Start_Date is between 12/1/2001 And 12/31/2001, OR if the Start_Date is before 12/1/2001, the End_Date is after 12/1/2001. Make sense, right?

Because to show records for employees who were working in Dec 2001, he must started working between Dec 1 to Dec 31 2001 or if he had started working before Dec 1, he must still be working after Dec 1, even only one day in Dec 2001.