From 1st of the year to now

[Sierras]

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..

 

[arthurjr07]

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

 

[Sierras]

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()

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?

 

[arthurjr07]

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

 

[arthurjr07]

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();

 

[Sierras]

That works very well!!

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

 

[Michel Walsh]

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