If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Year(Date()) not working
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! |
#3
|
|||
|
|||
Year(Date()) not working
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. |
#4
|
|||
|
|||
Year(Date()) not working
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] |
#5
|
|||
|
|||
Year(Date()) not working
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. . |
#6
|
|||
|
|||
Year(Date()) not working
Thanks, guys!!!
|
#7
|
|||
|
|||
Year(Date()) not working
"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) |
#8
|
|||
|
|||
Year(Date()) not working
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) |
#9
|
|||
|
|||
Year(Date()) not working
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] |
#10
|
|||
|
|||
Year(Date()) not working
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 - |
Thread Tools | |
Display Modes | |
|
|