Selecting records based on date

[emozley]

Hi,

I have a table that has fields FirstName, Surname and StartDate:

| Ed | Mozley | 14/10/2004 |
| John | Smith | 15/03/2007 |
| Jane | Smith | 02/10/2006 |

I want to run a report that shows all members of staff who joined in the range 1st October of any year to 31st October of any year - eg it should show Ed Mozley and Jane Smith but leave out John Smith.

Now as it is not always going to be October that I am looking at I am building the query dynamically using ASP. This brings certain limitations so the only way I can think of doing this is to do the following:

Find out what day number in the year 1st October is (273)
Find out what day number in the year 31st October is (303)

Select all records where the day number of the start date is between 273 and 303.

So far my query looks like this (but isn't working)

SELECT FirstName, Surname, StartDate
FROM Users
WHERE (DateDiff, "d", DatePart("yyyy", StartDate) & "/01/01") >= 273 AND DateDiff("d", DatePart("yyyy"), StartDate & "/01/01") <= 303
ORDER BY FirstName, ASC

Any help much appreciated!

Thanks very much

Ed

 

[JerryKlmns]

How about

SELECT FirstName, Surname, StartDate
FROM Users
WHERE Month(StartDate)= 10
ORDER BY FirstName;

That's for October....

 

[emozley]

Unfortunately can't do it just on month as the period I look at is always a three week period which sometimes covers two months and also would not necessarily run from the 1st of a given month.

My apologies I should have mentioned this in my first post.

E.

 

[JerryKlmns]

Getting closer

SELECT FirstName, Surname, StartDate
FROM Users
WHERE StartDate Between #2007-02-28# And #2007-09-26#
ORDER BY FirstName;

That' s a general syntax for a date period
an equal example using comparison operators is

WHERE StartDate >= #2007-02-28# And
StartDate <= #2007-09-26#

 

[emozley]

Sadly this won't work easier as by saying select records between x and y then it means there may be something that falls in that range but for another year and so would not be pulled up.

Since your last post I have done a bit more fiddling around and this is what I've got now - hopefully you will see what I mean - I've tested it and it works. In this case anyone who joined the company after the 1st October of any year:

SELECT FirstName, Surname, StartDate
FROM Users
WHERE DateDiff('d','01/01/' & DatePart('yyyy',StartDate),StartDate)>280
ORDER BY FirstName;

 

[JerryKlmns]

This removes the exact year info from the StartDate field
DateSerial(Year(Date());Month([StartDate]);Day([StartDate])

So this might give you the time span to filter?

 

[dhookom]

If you desire weeks then convert the date to the week of the year and set your criteria to:

SELECT FirstName, Surname, StartDate
FROM Users
WHERE DatePart("ww",StartDate) Between 12 And 14
ORDER BY FirstName;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]