By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Change format of date in Access

P: 41
Dear Community

My Access database contains a table with dates. For example 01.01.2006. (data type: "Date/Time")

That date is visible in a form in the front end. I only would like to have the year (2006) in the form and not the whole date. I know that this is possible, but I do not know how and where exactly to do that. Could you please help me?

Or should I change the data type into "text" and only fill in the year in the table without "01.01."? I am afraid, that if so I can't use this date for queries anymore...

Thanks a lot

Mike
Jul 31 '17 #1

✓ answered by PhilOfWalton

If only the year is relevant, then it's data type might as well be Integer.

You will need to perform checks that the date entered is reasonable.

Phil

Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
If the table genuinely contains Type Date/Time, then it should appear as 01/01/2006. So if you are seeing 01.01.2006 that has been formatted somewhere along the line (either in the table or in the form)

To only see the year, use
Expand|Select|Wrap|Line Numbers
  1. Format(MyDate, "yyyy")
  2.  
Phil
Jul 31 '17 #2

P: 41
Dear Phil

Thank you. Where exactly do I have to fill in "Format(MyDate, "yyyy")", is this under properties or where exactly?

Mike
Jul 31 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Assuming you have a form based on a Query, add a field to the query called MyYear (Year is a reserved word and should be avoided)

So in the Query to have:
Expand|Select|Wrap|Line Numbers
  1. MyYear:Format(MyDate, "yyyy")
  2.  
Then the control on the form is bound to MyYear. It should be locked

Phil
Jul 31 '17 #4

P: 41
Dear Phil

Unfortunately it is not a form that is based on a query but on a table. The table consists the date. Do you know how to it in this case?

Mike
Jul 31 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
Mike, I think I have pointed out before, you should NEVER be viewing tables. Everything should be viewed as a Form or reoport, and edited only on a form.

However, if you insist, with the table in Design View, on the on the General Tab --> Format type yyyy

Phil
Jul 31 '17 #6

P: 41
Dear Phil

I am sorry, maybe I did not write correctly what my point is... I have a form in which the enduser (frontend) should write in a year (e.g. 2006). In another part of the database (queries) I need exactly this year to calculate with it.

Because of that, the table that lays behind the form (the enduser does not work with the table, neither with the queries but only with forms and reports) has to be of the type Date/Time. When I define the type Date/Time, my problem is that the enduser has to put into the front end the format 01.01.2006 (instead of only 2006) and this long version is also shown on the form which is not convenient. In the table it would be ok.

Can you tell me how I can solve my problem?

Thanks again!

Mike
Jul 31 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
If only the year is relevant, then it's data type might as well be Integer.

You will need to perform checks that the date entered is reasonable.

Phil
Jul 31 '17 #8

P: 41
Dear Phil

Thanks a lot.

When I make it Integer (what means that it is the type number, right?), can I then still calculate with "years" in a query or do I have then to calculate like it would be a normal number?

Mike
Aug 2 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
Yes, it's just a number.

What calculations do you want in your queries?

Phil
Aug 2 '17 #10

P: 41
Dear Phil

Thenk it is okay. I just need to know that then, I can not calculate with dates because these numbers are not from the type date.

Thank you!!

Mike
Aug 2 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.