Solved

Change birthdate format

Posted on 2003-10-24
9
1,488 Views
Last Modified: 2008-03-17
I have a birthday field formatted as mm\dd\yyyy and I need to format it as ccyymmdd.  For example the existing date is 11/24/1929 and I need to change it to 19291124.  
0
Comment
Question by:Peppermint
9 Comments
 
LVL 4

Expert Comment

by:Ashen_Shugar
ID: 9613030

Simply change the format of the field in design view to yyyymmdd, this will retain all your current data but display it in the format you want.

To do this select the Table and go into design view, Select the date field and look at the properties below. The top one is format, change it the text above.



Stephen
0
 
LVL 2

Author Comment

by:Peppermint
ID: 9613059
I actually need to change the date and not just how it appears.  How do I do that?  Thanks
0
 
LVL 4

Expert Comment

by:Ashen_Shugar
ID: 9613110


Dates in Access are actually numbers that count the number of elapsed days since 30/12/1899.

For example the 01/01/2003 is actually 37622 as far as Access is concerned.

So you can only change how the date looks in tables and forms, using the Format.

This is OK though, because any calculations or work you do on the date field use the format information to see how it is displayed.

If you asked for the last two digits of 01/02/2003 when it is formated as yyyymmdd you would get 01 because Access knows the field looks like 20030201 to the user.

Is it any clearer now, if not let us know.





Stephen


0
 
LVL 77

Expert Comment

by:peter57r
ID: 9613114
Stephen's answer is all you need and can have.

Access does not store dates in any format, just as numbers.  YOU have to apply the format for display.

Pete
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Expert Comment

by:DoppyNL
ID: 9613121
>> For example the existing date is 11/24/1929 and I need to change it to 19291124

This is changing the format, not changing the date.

changing the format yyyymmdd will result in the display you want.

I think you have to be a little more specific on what you want, as to me the question seems answered, but it is not what you are looking for, so you want something else.
0
 
LVL 2

Author Comment

by:Peppermint
ID: 9613242
The trouble is I need to convert the date for the program.  It's ok if the data type is a number or text but it needs the value needs to be stored as 19291124.  Therefore, I have to take the current value of 11/24/1929 and eliminate the "/" and move the year to the beginning, etc.

I'm not exactly sure the reason behind storing the date in a format except to meet some European standard,  but dunno really. Needless to say, I have to actually change the value.  Hope this helps.

0
 
LVL 11

Accepted Solution

by:
joekendall earned 500 total points
ID: 9613344
Can you change the value in a query instead of the table?

If you want to change the value, you need a new field to hold the value you want to see and set it to text or number.

UPDATE MyTable
SET NewField = Format(OldDate,"yyyymmdd")

Joe
0
 

Expert Comment

by:AccessMD
ID: 9614300
Peppermint,

Make a table that has all the same fields as the original but with the Birthday field, change it to a text field. Then do an append query from the first table to the second.

In the birthday field put the following code instead of the field name:

NewBirthday: Right([Date],4) & Left([Date],2) & Mid([Date],4,2)

This will convert the original date to the format you want in a text field.

I hope this helps. If you need more info let me know.

MD
0
 
LVL 2

Author Comment

by:Peppermint
ID: 9615470
Thanks again Joe!!!  Please check the new questions, I have an easy one for ya!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now