Year(Date()) not working

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!

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" wrote in message
...
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!

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.

On Tue, 9 Feb 2010 11:31:33 -0800 (PST), johnlute wrote:

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

John W. Vinson [MVP]

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

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


"johnlute" wrote:

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

Thanks, guys!!!

"John W. Vinson" wrote in message
news

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

Good thinking, John!

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

(please reply to the newsgroup)

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.

On Feb 11, 2:38*pm, "Dirk Goldgar"
wrote:
"John W. Vinson" wrote in messagenews6h3n51lps53oao6rkha4rlgs6jli3rgmf@4ax .com...



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

Good thinking, John!

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

(please reply to the newsgroup)

On Fri, 12 Feb 2010 11:19:51 -0800 (PST), johnlute wrote:

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.

On Feb 11, 2:38*pm, "Dirk Goldgar"
wrote:
"John W. Vinson" wrote in messagenews6h3n51lps53oao6rkha4rlgs6jli3rgmf@4ax .com...



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

Good thinking, John!

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

(please reply to the newsgroup)
--

John W. Vinson [MVP]

Thanks again for the awesome tip, John!

On Feb 12, 4:06*pm, John W. Vinson
wrote:
On Fri, 12 Feb 2010 11:19:51 -0800 (PST), johnlute wrote:

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.

On Feb 11, 2:38*pm, "Dirk Goldgar"
wrote:
"John W. Vinson" wrote in messagenews6h3n51lps53oao6rkha4rlgs6jli3rgmf@4ax .com...

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

Good thinking, John!

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

(please reply to the newsgroup)

--

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

- Show quoted text -