Easty
asked on
converting dates to 1900+ rather than 2000+
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.
Can anyone tell me how to convert to dates before 1930.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
<<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.
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.
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);
UPDATE birthdates SET birthdates.dob = DateAdd("yyyy",-100,[dob])
WHERE Year([dob]) > year(now);
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
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
ASKER
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.
iif([date of birth] >01/01/2002, dateadd("yyyy", -100, [date of birth]), [date of birth])
cheers.
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.