Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

converting dates to 1900+ rather than 2000+

Posted on 2002-04-12
7
Medium Priority
?
249 Views
Last Modified: 2012-05-04
I am importing data from a text file that has mixture of dates in long format i.e. dd/mm/yyyy and short dates i.e. dd/mm/yy in one field. The field is a date of birth field, and it has people in it with date of birth before 1930. When i change the field to a date format, it changes there date of births to 2000+.
Can anyone tell me how to convert to dates before 1930.
0
Comment
Question by:Easty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 58
ID: 6936334
Which version of Access?  If it's 97 or later, there is nothing you can do about it in terms of changing the import behavior.

Couple of things then:
1. Change the input file.

2. Change the data after import with a query.

3. Import the file using a custom VBA procedure and modify the dates as they are read in.

 #3 is probably the best (but slightly more work - however you get complete control over the process), but I don't know if your importing into an existing table with data or not.  Also, I don't know if you can change the input file.

Jim.

0
 
LVL 18

Accepted Solution

by:
deighton earned 210 total points
ID: 6936369
After you import your file, run an update query, something like below. 2002 represents the latest year you can be born in.  If you have no children in your table, then you might want to change that to 1990 (for example)


UPDATE birthdates SET birthdates.dob = DateAdd("yyyy",-100,[dob])
WHERE Year([dob]) > 2002;



this finds dates that have converted to 2030 (say) and corrects them to 1930.
0
 

Author Comment

by:Easty
ID: 6936377
i am using access 2000.

changing the input file isn't really an option

The data is first imported into a table in access, then a  make table query is currently run on the data, this is the stage at which the dates are changed.

Is there any formatting you can do on the CDate function to import 2000 dates as 1900.
I notice that if i import the text file into excel first the dates are correctly changed, but i would prefer to bypass this stage as it creates an extra step and i'd like to automate the process as much as possible.

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 58
ID: 6936402
<<i am using access 2000.>>

Ok.

<<changing the input file isn't really an option>>

Got it.

<<The data is first imported into a table in access, then a  make table query is currently run on the
data, this is the stage at which the dates are changed.

Is there any formatting you can do on the CDate function to import 2000 dates as 1900.
>>

  Sounds like your importing it as a text field.  If so that's good as you can run an update query to modify all the 2 digit dates <30 to 4 digits.  Then run your make table.


<<I notice that if i import the text file into excel first the dates are correctly changed, but i would
prefer to bypass this stage as it creates an extra step and i'd like to automate the process as much
as possible.>>

  You can do this in Access, but not witht he import wizard the way it is.  The rule for 2 digit years is that anything starting with 30 and up is assumed to be 2000, which you cannot change.  Excel is acting the opposite and assuming 1900.  Access used to be this way, but was changed with Access 97.  What are you guys doing in Excel with a birthdate of '01, which should be 2001 and not 1901?

Jim.
0
 
LVL 18

Expert Comment

by:deighton
ID: 6936489
How about my earlier idea of a running a query to knock 100 off any dates greater than 2002

UPDATE birthdates SET birthdates.dob = DateAdd("yyyy",-100,[dob])
WHERE Year([dob]) > year(now);
0
 
LVL 58
ID: 6936523
deighton,

 That won't work.  He needs to know which dates are 2 digit vs 4 so he can determine which need to be reduced

For example, if he imports:

01
2001

He would get:
2001
2001

 and it should be:

1901
2001
0
 

Author Comment

by:Easty
ID: 6936682
this seemed to be the best solution. Edited it slightly so it was in an if statement in the form of
iif([date of birth] >01/01/2002, dateadd("yyyy", -100, [date of birth]), [date of birth])

cheers.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

704 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