Changing cut off date on Access

jtequia
jtequia used Ask the Experts™
on
I have DOB text field that's formatted mm-dd-yy, I need to convert the text into a valid date field, however in the case where the yy is < = 30 (1930) it returns a 2030.   How can set my cutoff year to say 2012?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
CDate("4-4-1929")

returns
04-04-1929

mx
Top Expert 2011

Commented:
What version off Access?

AFAIK, This is a machine setting, not an Access setting. It is part of the Regional settings.


Author

Commented:
2010
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You can try this also:

DateSerial (Year([YourDate]),month([YourDate]),day([YourDate]))

mx
Most Valuable Expert 2014

Commented:
You could knock the date into a Split array and then handle it exactly the way you want.

Dim MyFinalDate as Date
Dim TheYear as integer
Dim TheMonth as integer
Dim TheDay as integer

'split the date using the split function
Dim DateSplit() as string 'an array to hold the date elements
DateSplit = Split("4-4-29", "-")  '--> throw in a variable or whatever instead of 4-4-29
TheDay = CInt(DateSplit(0)) 'coerce the day string to integer
TheMonth=CInt(DateSplit(1)) 'coerce the month string to integer
'test the year, between 0 and 12, pre-pend '20', else pre-pend '19' then coerce to intger
TheYear=  CInt(IIf(CInt(DateSplit(2)) > 0 and  CInt(DateSplit(2)) <13, CInt("20" & DateSplit(2)), CInt("19" & DateSplit(2))))

'now knock it together in a bullet-proof way
MyFinalDate=DateSerial (Year(TheYear),month(TheMonth),day(TheDay))
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Here is how to display:

DateOfBirth: DateSerial(Year([DOB])-((Year([DOB])-13)\1000-1)*100,Mid([DOB],1,2),Mid([DOB],4,2))

Or create an update query where you use the expression to set the value of the new date field.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial