Date from number string

[Barry]

Hi

I have a field where a 13 digit number would be enter, IDNumber, example,
7608155095088.

The first 6 digit are the date of birth YYMMDD, what I want to happen is
when the user exits that field the date of birth will automatically be
inserted into the DateOfBirth field with a format 19YY/MM/DD.

Thanks for any help.


Regards

Barry

 

[John Vinson]

Hi

I have a field where a 13 digit number would be enter, IDNumber, example,
7608155095088.

Eeeuwww... you're CRUEL to your users. They must memorize and type in
a 13-digit composite numeric key? Do you have a checkdigit to catch
the inevitable typographical errors? Sigh... ok... it's bad design,
but if that's how your table is structured... However this MUST be a
Text field; it's too big for a Long Integer and almost too big for a
Double.

The first 6 digit are the date of birth YYMMDD, what I want to happen is
when the user exits that field the date of birth will automatically be
inserted into the DateOfBirth field with a format 19YY/MM/DD.

or of course 18yy/mm/dd or 20yy/mm/dd... try

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

You can then set the Format of the textbox in which this DateTime
field (internally stored as a double float count of days since
midnight, December 30, 1899) to yyyy/mm/dd in order to get your
desired display.

 

[Greg Kraushaar]

intYear = cint(Left(IDNum, 2))
intMth = cint(mid(... you get the idea

dtm = DateSerial(intYr, intMth,intDay)
rst!DOB = dtm

If you want to do it in a query, you will need to compress all the
lines

Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)

 

[Barry]

The 13 Digit Number is something like a Social Security number, every one in
South Africa has a unique ID Number, so the government is cruel, not me

We are capturing data from application forms, I thought it would be easier
to fill in the date automatically than having to retype it.

The field is already text, we are inputting data for people between 18 and
79 so all date of birth will begin with 19, until 2018, so no worries there
just yet.

I tried to put the code in the on enter property but get a compile error:
Expected Identifier.

Private Sub DateOfBirth_Enter()
CDate(Mid([IDNumber], 3, 2) & "/" & Mid([IDNumber], 5, 2) & "/" &
Mid([IDNumber], 1, 2))
End Sub


Thanks

Barry

Hi

I have a field where a 13 digit number would be enter, IDNumber, example,
7608155095088.

Eeeuwww... you're CRUEL to your users. They must memorize and type in
a 13-digit composite numeric key? Do you have a checkdigit to catch
the inevitable typographical errors? Sigh... ok... it's bad design,
but if that's how your table is structured... However this MUST be a
Text field; it's too big for a Long Integer and almost too big for a
Double.

The first 6 digit are the date of birth YYMMDD, what I want to happen is
when the user exits that field the date of birth will automatically be
inserted into the DateOfBirth field with a format 19YY/MM/DD.

or of course 18yy/mm/dd or 20yy/mm/dd... try

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

You can then set the Format of the textbox in which this DateTime
field (internally stored as a double float count of days since
midnight, December 30, 1899) to yyyy/mm/dd in order to get your
desired display.

 

[Barry]

Got it to work using this:

Private Sub DateOfBirth_Enter()
Me.DateOfBirth = (Mid([IDNumber], 3, 2) & "/" & Mid([IDNumber], 5, 2) & "/"
& Mid([IDNumber], 1, 2))
End Sub

Thanks a million! And my users are now worshipping my cruel feet!!
;-)

Hi

I have a field where a 13 digit number would be enter, IDNumber, example,
7608155095088.

Eeeuwww... you're CRUEL to your users. They must memorize and type in
a 13-digit composite numeric key? Do you have a checkdigit to catch
the inevitable typographical errors? Sigh... ok... it's bad design,
but if that's how your table is structured... However this MUST be a
Text field; it's too big for a Long Integer and almost too big for a
Double.

The first 6 digit are the date of birth YYMMDD, what I want to happen is
when the user exits that field the date of birth will automatically be
inserted into the DateOfBirth field with a format 19YY/MM/DD.

or of course 18yy/mm/dd or 20yy/mm/dd... try

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

You can then set the Format of the textbox in which this DateTime
field (internally stored as a double float count of days since
midnight, December 30, 1899) to yyyy/mm/dd in order to get your
desired display.

 

[John Vinson]

Got it to work using this:

Private Sub DateOfBirth_Enter()
Me.DateOfBirth = (Mid([IDNumber], 3, 2) & "/" & Mid([IDNumber], 5, 2) & "/"
& Mid([IDNumber], 1, 2))
End Sub

If you use the ID field's AfterUpdate event it'll be even easier -
they won't need to even set the focus to the DateOfBirth field.

And I'm mollified about the data entry chore... somewhat! <g>

 

[Barry]

I do have a slight problem though, how do I stop this code running if the
IDNumber is null, I get a debug error.

Any suggestions?

Got it to work using this:

Private Sub DateOfBirth_Enter()
Me.DateOfBirth = (Mid([IDNumber], 3, 2) & "/" & Mid([IDNumber], 5, 2) & "/"
& Mid([IDNumber], 1, 2))
End Sub

If you use the ID field's AfterUpdate event it'll be even easier -
they won't need to even set the focus to the DateOfBirth field.

And I'm mollified about the data entry chore... somewhat! <g>