[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Year(Now()) is returning wrong value in a query.

I'm working on a database to track camp registration and I want to restrict some reports to the current year.  I am trying to filter records in a query by using the start date for the camp [BegDate], extracting the year and comparing it to the current year using Year(Now()).  The problem is that Year (Now()) is returning 1905 instead of 2006.  

I know there's some feature in Access that determines how dates will be read but I can't remember what or where.  Is there something I need to change in my settings or is there another way to do what I'm trying to do?





0
fernw
Asked:
fernw
  • 7
  • 3
  • 3
  • +5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Very Wild guess)  Double-click on the time in the lower right of your screen/Windows Taskbar, and make sure the year is not set to 1905.
0
 
peter57rCommented:
Hi fernw,
I'm with Jim.
I could just about come up with a reason to get 1906 but as for 1905 that is just impossible if the machine date is correct.

Pete
0
 
Jim P.Commented:
1. Check your systems date/time
2. Check that you don't have any missing references Ctrl+G  on the menu Tools --> References see if anything says missing
3. Try Year(Date())
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
fernwAuthor Commented:
Hi Jim and Pete,

I checked my computer date as suggested and it says April 27, 2006.

I just went back to my query and copied the calculated field.  Here it is:

          CurrYear: Year(Now())

I also tried   CurrYear: Year(Date())  and got the same result:  1905

Is that just plain weird or is there something wrong in the syntax of my calculated field?

Any other suggestions?

Fern
0
 
peter57rCommented:
If you just put a column:
mydate:Date()
into a query what do you get?


Pete
0
 
fernwAuthor Commented:
mydate:Date()  returns 4/27/2006
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Another wild guess)  Make sure you do not have any global variables named Now or Date, or have a public function named Year.
0
 
GRayLCommented:
In the Immediate Pane:

? Now()
What datetime appears?
0
 
FlysterCommented:
Hi fernw,

Have you tried: Format(Now(),"yyyy")

Flyster
0
 
SYED NABEEL SHAHIDCommented:
Hi,

Please make sure that the default date format in your system is "mm/dd/yyyy" or "dd/mm/yyyy".

or use this  

 format(Date(),"yyyy")


Thanks,
Syed Nabeel.
0
 
harfangCommented:
Strange indeed.

One more wild guess:

Have you changed the calendar property? For example, Year(Now()) returns 1427 if you are using the Hijri calendar. I know later versions (I have A2k here) support more calendars. Does that ring a bell?

Good luck!
(°v°)
0
 
fernwAuthor Commented:
Thanks to all who have replied since my last response. I had to leave the office yesterday afternoon and just got back to it.

Flysters suggest to use Format (Now(),"yyyy") works.

I'd still like to know why Year (Now(),"yyyy") doesn't work, but my query is doing what I want it to do using Format (Now()....) so will award points to Flyster.

Thanks to everyone who offered suggestions.  I really appreciate all your efforts.

Fern



0
 
GRayLCommented:
It has to be Year(Now()).  In the Immediate Pane:

? now()
2006-04-28 11:32:50
? year(now())
 2006


0
 
fernwAuthor Commented:
I looked back at my original question and Year(Now()) is what I was using and getting 1905.
Then I changed it to Year(Date()) and still got 1905.
However,  I tried Year(Now()) again and got 2006.  Year(Date()) is still returning 1905.

2 questions:

Can you get to the immediate pane from a query or do you have to go into VB?

What is the difference between Date() and Now().  It looks like the only difference is that Now returns the current date and time and Date can be any date and time.  Can they be used interchangably to return current date?

0
 
Jim P.Commented:
The Date() function returns only the date without the time. The Time() function returns the current time without a date.

Now() returns both date and time.
0
 
fernwAuthor Commented:
Thanks, jimpen

That's very helpful.
Can you suggest why Year(Date()) would return 1905 instead of 2006?

I'd be glad to award points for answers but I'm not sure how to go about it since original question points have already been awarded.  Maybe I should start a new question.
0
 
Jim P.Commented:
Date is a reserved keyword in access. So if you have a field or some other function simply named Date then access can be misreading from that field or other function.
0
 
fernwAuthor Commented:
I don't think I have anything called just Date.  I always name things xxxDate e.g. BegDate for Beginning Date so that I know what they refer to.  But I'll check it out.

Thanks once again.

Fern
0
 
GRayLCommented:
You can get to the Immediate Pane by typing Ctl-G or Alt-F11.  Go back to the database window by clicking on the Access icon on the toolbar in the VB Editor.
0
 
fernwAuthor Commented:
Thanks!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now