Date Query (Access 97)

Topic

New Reply

I have a database that we use to keep up with employees who are delinquent in their training. I am not concerned with MM/DD/YY format to extract people. I want Month only. Our fiscal years runs from Oct to Sept. I want people from 11 (Nov) to 5 (May) to print on my report. Currently, the query is pulling from 5 to 11.
Is there a way to make it work the way I need it? I need it to run in our fiscal year. The training is based on their hire date.

Please help!

Deborah

Reply | Quote

Replies

Try something like this, where DateField is the name of the date field you want to work with:
– Create a query based on the table(s) you need, or on a query you already have.
– Add a calculated column Year(DateAdd(“m”, 3, [DateField]) and set the criteria to the desired year, where Oct 2003 – Sep 2004 counts as 2004, Oct 2004 – Sep 2005 as 2005 etc.
– Add another calculated column Month(DateAdd(“m”, 3, [DateField]) and set the criteria to Between 2 And 8
– Switch to datasheet view to check that the correct records are returned.
– Switch back to design view and clear the “Show” check box for the calculated columns.
The trick behind this is to add 3 months to the date, so that the fiscal year Oct-Sep becomes a calendar year Jan-Dec.

Reply | Quote

Try something like this, where DateField is the name of the date field you want to work with:
– Create a query based on the table(s) you need, or on a query you already have.
– Add a calculated column Year(DateAdd(“m”, 3, [DateField]) and set the criteria to the desired year, where Oct 2003 – Sep 2004 counts as 2004, Oct 2004 – Sep 2005 as 2005 etc.
– Add another calculated column Month(DateAdd(“m”, 3, [DateField]) and set the criteria to Between 2 And 8
– Switch to datasheet view to check that the correct records are returned.
– Switch back to design view and clear the “Show” check box for the calculated columns.
The trick behind this is to add 3 months to the date, so that the fiscal year Oct-Sep becomes a calendar year Jan-Dec.

Reply | Quote

You don’t show the SQL you are using, but I would say the following is a generic solution:

SELECT EmployeeID, EmployeeName
FROM tblEmployees
WHERE Month(DateOfHire) In (11,12,1,2,3,4,5)

Or:

SELECT EmployeeID, EmployeeName
FROM tblEmployees
WHERE Month(DateOfHire) =11

Reply | Quote

You don’t show the SQL you are using, but I would say the following is a generic solution:

SELECT EmployeeID, EmployeeName
FROM tblEmployees
WHERE Month(DateOfHire) In (11,12,1,2,3,4,5)

Or:

SELECT EmployeeID, EmployeeName
FROM tblEmployees
WHERE Month(DateOfHire) =11

Reply | Quote