Problems sorting dates in a query

[Zulema]

I am working on a query that will sort all employees that have been hired after a certain date and have not been terminated within a certain date. (Hope this doesn't sound too complicated!) I am working on September statement: which means that i need the query to spit out employees hired before 07/01/07, but not to include them if they have a termination date earlier than 08/31/2007. What I have so far is:
HAVING (((EMPLOYEES.[HIRE DATE]) Between #1/1/1972# And [Hires thru what date?]) AND ((EMPLOYEES.SECTION)=[What Section?]) AND ((EMPLOYEES.[VLIFE DEDUCTION])<>0)) OR (((EMPLOYEES.[TERMINATION DATE])>[Terminations thru what date?]))
ORDER BY EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE;

[DThreadgill]

I am working on a query that will sort all employees that have been hired after a certain date and have not been terminated within a certain date. (Hope this doesn't sound too complicated!) I am working on September statement: which means that i need the query to spit out employees hired before 07/01/07, but not to include them if they have a termination date earlier than 08/31/2007. What I have so far is:
HAVING (((EMPLOYEES.[HIRE DATE]) Between #1/1/1972# And [Hires thru what date?]) AND ((EMPLOYEES.SECTION)=[What Section?]) AND ((EMPLOYEES.[VLIFE DEDUCTION])<>0)) OR (((EMPLOYEES.[TERMINATION DATE])>[Terminations thru what date?]))
ORDER BY EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE;

Zulema
The user could enter the date as 1/1/2007 or 01/01/2007 so the dates need to be set to a consistant format: Format([hire date],"mm/dd/yyyy" This would apply to your parameter fields as well.

Hope this helps.

[mlcampeau]

I am working on a query that will sort all employees that have been hired after a certain date and have not been terminated within a certain date. (Hope this doesn't sound too complicated!) I am working on September statement: which means that i need the query to spit out employees hired before 07/01/07, but not to include them if they have a termination date earlier than 08/31/2007. What I have so far is:

HAVING (((EMPLOYEES.[HIRE DATE]) Between #1/1/1972# And [Hires thru what date?]) AND ((EMPLOYEES.SECTION)=[What Section?]) AND ((EMPLOYEES.[VLIFE DEDUCTION])<>0)) OR (((EMPLOYEES.[TERMINATION DATE])>[Terminations thru what date?]))
ORDER BY EMPLOYEES.LAST, EMPLOYEES.FIRST, EMPLOYEES.MIDDLE;

Try this:

HAVING (([Employees.HireDate] Between #1/1/1972# AND (CDate([Hires thru what date?]))) AND [Employees.Section]=[What Section?] AND [Employees.VLife Deduction]<>0 AND ([Employees.TerminationDate] Is Null OR [Employees.TerminationDate]>CDate([Terminations thru what date?])))

[Zulema]

Zulema
The user could enter the date as 1/1/2007 or 01/01/2007 so the dates need to be set to a consistant format: Format([hire date],"mm/dd/yyyy" This would apply to your parameter fields as well.

Hope this helps.

I need it to list all records with hire dates up to a certain date, but to disregard the record if the record has a termination date earlier than the date the user inputs when prompted....hope this makes sense!

[Zulema]

My query looks like this:dateststqry

SELECT EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], Last(EMPLOYEES.LAST) AS LastOfLAST, EMPLOYEES.FIRST
FROM EMPLOYEES
GROUP BY EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], EMPLOYEES.FIRST, NZ([TERMINATION DATE])
HAVING (((EMPLOYEES.[HIRE DATE])<=[THRU WHAT HIRE DATE?]) AND ((NZ([TERMINATION DATE]))<=#9/30/2007#))
ORDER BY Last(EMPLOYEES.LAST), EMPLOYEES.FIRST;

results:

HIRE DATE SECTION TERMINATION DATE LastOfLAST FIRST
1/1/1991 Admin 6/30/2007 Last First
11/19/2001 Admin 10/18/2007 Last First
03/2/1999 Admin Last First
09/13/2007 Admin Last First

I need it to exclude the rows with termination dates, but not if the termination date is later than the date that the user enters when prompted for a date....

[mlcampeau]

My query looks like this:dateststqry

SELECT EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], Last(EMPLOYEES.LAST) AS LastOfLAST, EMPLOYEES.FIRST
FROM EMPLOYEES
GROUP BY EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], EMPLOYEES.FIRST, NZ([TERMINATION DATE])
HAVING (((EMPLOYEES.[HIRE DATE])<=[THRU WHAT HIRE DATE?]) AND ((NZ([TERMINATION DATE]))<=#9/30/2007#))
ORDER BY Last(EMPLOYEES.LAST), EMPLOYEES.FIRST;

results:

HIRE DATE SECTION TERMINATION DATE LastOfLAST FIRST
1/1/1991 Admin 6/30/2007 Last First
11/19/2001 Admin 10/18/2007 Last First
03/2/1999 Admin Last First
09/13/2007 Admin Last First

I need it to exclude the rows with termination dates, but not if the termination date is later than the date that the user enters when prompted for a date....

Zulema,
Did you try my suggestion in Post#3? If so, what were the results? Here's my suggestion again, incorporated into your entire sql statement (in the future please wrap your code in Code tags by highlighting the code, and clicking on the button with the # sign)

SELECT EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], Last(EMPLOYEES.LAST) AS LastOfLAST, EMPLOYEES.FIRST
FROM EMPLOYEES
GROUP BY EMPLOYEES.[HIRE DATE], EMPLOYEES.SECTION, EMPLOYEES.[TERMINATION DATE], EMPLOYEES.FIRST, 
HAVING HAVING (([Employees.HireDate]<=(CDate([Hires thru what date?]))) AND [Employees.Section]=[What Section?] AND [Employees.VLife Deduction]<>0 AND ([Employees.TerminationDate] Is Null OR [Employees.TerminationDate]>CDate([Terminations thru what date?])))
ORDER BY Last(EMPLOYEES.LAST), EMPLOYEES.FIRST;

[Zulema]

It worked!!! Thank you very much!

[mlcampeau]

Glad you got it working!