Year(Date()) not working

[johnlute]

Access 2003 Win 2000.

SELECT tblCustomerComplaints.DateReceived
FROM tblCustomerComplaints
WHERE (((tblCustomerComplaints.DateReceived)=Year(Date())));

The Year(Date)) isn't returning the current year as I *think* it
should. It returns NULL.

Can anyone please point out what I'm doing wrong?

Thanks!

 

[Jeff Boyce]

Given the name of the field ([DateReceived]), is it reasonable to assume
that the value is a date/time value?

If so, why are you comparing it to the Year-value of today's date (i.e.,
Year(Date()))?

(?are you trying to test the value in [DateReceived] to see if it is in the
same year as the current date? if so, that's not how you'd do that...)

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[johnlute]

Hi, Jeff.

Given the name of the field ([DateReceived]), is it reasonable to assume
that the value is a date/time value?

Yes. Short Date format.

If so, why are you comparing it to the Year-value of today's date (i.e.,
Year(Date()))?

Well, I was reading a bunch of posts regarding "current year" were
answered with "use Year(Date())". Obviously, I'm not very familiar
with querying dates.

(?are you trying to test the value in [DateReceived] to see if it is in the
same year as the current date?  if so, that's not how you'd do that...)

I just want the query to return all of the records of the current year.

 

[John W. Vinson]

Hi, Jeff.

Given the name of the field ([DateReceived]), is it reasonable to assume
that the value is a date/time value?

Yes. Short Date format.

If so, why are you comparing it to the Year-value of today's date (i.e.,
Year(Date()))?

Well, I was reading a bunch of posts regarding "current year" were
answered with "use Year(Date())". Obviously, I'm not very familiar
with querying dates.

(?are you trying to test the value in [DateReceived] to see if it is in the
same year as the current date?  if so, that's not how you'd do that...)

I just want the query to return all of the records of the current year.

Use a criterion

= DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()) + 1, 1, 1)

to find all date values during the current year (and to use any indexes on
your DateReceived field).

A bit simpler but less efficient would be to put a calculated field in your
query:

YearReceived: Year([DateReceived])

and use a criterion on this of

=Year(Date())

 

[Jerry Whittle]

WHERE Year([DateReceived])=Year(Date());

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi, Jeff.

Given the name of the field ([DateReceived]), is it reasonable to assume
that the value is a date/time value?

Yes. Short Date format.

If so, why are you comparing it to the Year-value of today's date (i.e.,
Year(Date()))?

Well, I was reading a bunch of posts regarding "current year" were
answered with "use Year(Date())". Obviously, I'm not very familiar
with querying dates.

(?are you trying to test the value in [DateReceived] to see if it is in the
same year as the current date? if so, that's not how you'd do that...)

I just want the query to return all of the records of the current year.
.

 

[johnlute]

Thanks, guys!!!

 

[Dirk Goldgar]

Use a criterion


to find all date values during the current year (and to use any indexes on
your DateReceived field).

Good thinking, John!

 

[johnlute]

I went with this because it uses the indexes. I tried Jerry's WHERE
Year([DateReceived])=Year(Date()); but there was a slight drag. Hardly
noticeable but as the data grows it will surely become more
noticeable.

I tried John's and there was no drag.

 

[John W. Vinson]

I've got some date range queries on my (biggish, 300000 row) animal shelter
tables. With an index on the date field (critical!!!) and the date range
criteria, there's no noticable delay for queries. Using the calculated
Year(Date()) it can take minutes, since a) Access must do a full table scan,
retrieving records from 1997 and on and then rejecting most of them and b)
must call a function on every row.

I went with this because it uses the indexes. I tried Jerry's WHERE
Year([DateReceived])=Year(Date()); but there was a slight drag. Hardly
noticeable but as the data grows it will surely become more
noticeable.

I tried John's and there was no drag.

Good thinking, John!

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[johnlute]

Thanks again for the awesome tip, John!

I've got some date range queries on my (biggish, 300000 row) animal shelter
tables. With an index on the date field (critical!!!) and the date range
criteria, there's no noticable delay for queries. Using the calculated
Year(Date()) it can take minutes, since a) Access must do a full table scan,
retrieving records from 1997 and on and then rejecting most of them and b)
must call a function on every row.

I went with this because it uses the indexes. I tried Jerry's WHERE
Year([DateReceived])=Year(Date()); but there was a slight drag. Hardly
noticeable but as the data grows it will surely become more
noticeable.

I tried John's and there was no drag.

--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -