I have a large database where the Date of birth has been set to type integer
of the form dd/mm/yy. I need to perform some calculations on the dates but
when I try to just change the type to date it seems to come up with a totally
different date.

Can anyone suggest how these can be converted and still keep the correct
values?

Thanks

How are you attempting to convert the integers, and how are you attempting
to use the resultant dates?

If you're trying to use the dates in SQL statements (which includes the
Domain Aggregate functions such as DLookup), regardless of what your Short
Date format has been set to through Regional Settings, you must use
mm/dd/yyyy format (or else an unambiguous one such as yyyy-mm-dd or dd mmm
yyyy)

You may want to read Allen Browne's "International Dates in Access" at
http://www.allenbrowne.com/ser-36.html, or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". (You can
download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ajrbruce" <(E-Mail Removed)> wrote in message
news:80C04EE0-0653-4ABC-A56A-(E-Mail Removed)...
>I have a large database where the Date of birth has been set to type
>integer
> of the form dd/mm/yy. I need to perform some calculations on the dates but
> when I try to just change the type to date it seems to come up with a
> totally
> different date.
>
> Can anyone suggest how these can be converted and still keep the correct
> values?
>
> Thanks

In news:80C04EE0-0653-4ABC-A56A-(E-Mail Removed),
Ajrbruce <(E-Mail Removed)> wrote:
> I have a large database where the Date of birth has been set to type
> integer of the form dd/mm/yy.

Integer? Not Long Integer? An Integer field in Access/Jet can't hold
6-digit numbers. Unless this database is in some other file format, I'm
going to assume you mean Long Integer.

> I need to perform some calculations on
> the dates but when I try to just change the type to date it seems to
> come up with a totally different date.
>
> Can anyone suggest how these can be converted and still keep the
> correct values?

Do you mean that today's date, for example, would be stored as 290507?
If so, you might try an expression like

CDate(Mid(CStr([DOB]), 3, 2) & "/" &
Mid(CStr([DOB]), 1, 2) & "/" &
Mid(CStr([DOB]), 5, 2))

where DOB is the name of the field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In news:80C04EE0-0653-4ABC-A56A-(E-Mail Removed),
> Ajrbruce <(E-Mail Removed)> wrote:
>> I have a large database where the Date of birth has been set to type
>> integer of the form dd/mm/yy.
>
> Integer? Not Long Integer? An Integer field in Access/Jet can't hold
> 6-digit numbers. Unless this database is in some other file format, I'm
> going to assume you mean Long Integer.
>
>> I need to perform some calculations on
>> the dates but when I try to just change the type to date it seems to
>> come up with a totally different date.
>>
>> Can anyone suggest how these can be converted and still keep the
>> correct values?
>
> Do you mean that today's date, for example, would be stored as 290507? If
> so, you might try an expression like
>
> CDate(Mid(CStr([DOB]), 3, 2) & "/" &
> Mid(CStr([DOB]), 1, 2) & "/" &
> Mid(CStr([DOB]), 5, 2))
>
> where DOB is the name of the field.

Since the OP is wanting to use dd/mm/yy, odds are that's the Short Date
format on the machine (as set through Regional Settings).

That means that while

CDate(Mid(CStr([DOB]), 3, 2) & "/" &
Mid(CStr([DOB]), 1, 2) & "/" &
Mid(CStr([DOB]), 5, 2))

will work fine for a value of 290507 for DOB, it will result in 06 Jan,
2007, not 01 Jun, 2007, when the value for DOB is 010607.

Far safer is to use the DateSerial function. However, that does require a 4
digit year, so some logic will have to be used to determine whether the year
should be in the 1900s or the 2000s. If we're dealing with DOB, it's likely
legimate to use:

DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

On Tue, 29 May 2007 12:47:19 -0400, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>Far safer is to use the DateSerial function. However, that does require a 4
>digit year, so some logic will have to be used to determine whether the year
>should be in the 1900s or the 2000s. If we're dealing with DOB, it's likely
>legimate to use:
>
>DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
>Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
>CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))
>
>

I was curious so I checked: I knew DateSerial was a clever bit of code, but
it's even cleverer than I thought!

?dateserial(7,5,29)
5/29/2007
?dateserial(29,1,1)
1/1/2029
?dateserial(30,1,1)
1/1/1930

so it uses the familiar two/four digit convention.

Dates of birth *really* need to be four digit. There are lots of centenarians
around now, and being born in '05 (or even in '98) is still ambiguous!

John W. Vinson [MVP]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Tue, 29 May 2007 12:47:19 -0400, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>
>>Far safer is to use the DateSerial function. However, that does require a
>>4
>>digit year, so some logic will have to be used to determine whether the
>>year
>>should be in the 1900s or the 2000s. If we're dealing with DOB, it's
>>likely
>>legimate to use:
>>
>>DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
>>Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
>>CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))
>>
>>
>
> I was curious so I checked: I knew DateSerial was a clever bit of code,
> but
> it's even cleverer than I thought!
>
> ?dateserial(7,5,29)
> 5/29/2007
> ?dateserial(29,1,1)
> 1/1/2029
> ?dateserial(30,1,1)
> 1/1/1930
>
> so it uses the familiar two/four digit convention.

Actually, it makes sense that it would use whatever's defined on the Date
tab under Customer Regional Options for the "When a two-digit year is
entered, interpret it as a year between",

> Dates of birth *really* need to be four digit. There are lots of
> centenarians
> around now, and being born in '05 (or even in '98) is still ambiguous!

No argument from me on this point!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

In news:%(E-Mail Removed),
Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
>
> Since the OP is wanting to use dd/mm/yy, odds are that's the Short
> Date format on the machine (as set through Regional Settings).

Good point, Doug. I forgot that CDate would use the regional settings.
I was thinking it was like Jet's interpretation of date literals.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)