From 1st of the year to now

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sierras - 29 May 2006 00:15 GMT

I'm trying to make a query with the criteria from the 1st day of the
current year to now. Right now, I am using the criteria:

Between [EnterStart Date] And Now()

But the user always uses the first of the year of the current year.
Anyway to automate this so that they don't have to enter any start
date and have the query always start at the 1st day of the current
year?

Thanks..

Reply to this Message

arthurjr07@gmail.com - 29 May 2006 00:56 GMT

SELECT *
FROM Employees
WHERE BirthDate Between
CDate("01/01/" & Year(NOW)) AND NOW()

Reply to this Message

Sierras - 29 May 2006 02:52 GMT

Thanks - that worked great!!

I've got another user who needs the same thing but their fiscal year
starts on the 1st of Aug. Do you have a similar code for a start
date for the previous 1st of Aug to Now?

Thanks again..

>SELECT *
>FROM Employees
>WHERE BirthDate Between
>CDate("01/01/" & Year(NOW)) AND NOW()

Reply to this Message

arthurjr07@gmail.com - 29 May 2006 03:39 GMT

SELECT *
FROM Employees
WHERE BirthDate Between
CDate("08/01/" & IIF(Month(Now()) =>
8,Year(NOW()),Cstr(Cint(Year(NOW())-1))))
And NOW();

Reply to this Message

arthurjr07@gmail.com - 29 May 2006 03:47 GMT

if you will encounter a syntax error,
try to replace => with >=

it should be like this..

SELECT *
FROM Employees
WHERE BirthDate Between
CDate("08/01/" & IIF(Month(Now()) >=
8,Year(NOW()),Cstr(Cint(Year(NOW())-1))))
And NOW();

Reply to this Message

Sierras - 29 May 2006 05:16 GMT

That works very well!!

Thank you so much for taking the time to help me out.
Great code!!

>if you will encounter a syntax error,
>try to replace => with >=
[quoted text clipped - 7 lines]
>8,Year(NOW()),Cstr(Cint(Year(NOW())-1))))
>And NOW();

Reply to this Message

Michel Walsh - 29 May 2006 12:43 GMT

Hi,

You can also subtract 8 months:

SELECT *
FROM tableName
WHERE Year(DateAdd("m", -8, dateStamp)) = Year(now())

Hoping it may help,
Vanderghast, Access MVP

> SELECT *
> FROM Employees
> WHERE BirthDate Between
> CDate("08/01/" & IIF(Month(Now()) =>
> 8,Year(NOW()),Cstr(Cint(Year(NOW())-1))))
> And NOW();

Reply to this Message