Date from number string

  • Thread starter Thread starter Barry
  • Start date Start date
B

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
 
Hi

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

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.
Click to expand...

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.
 
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)
 
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



John Vinson said:
Hi

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

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.
Click to expand...

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.
Click to expand...
 
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!!
;-)

John Vinson said:
Hi

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

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.
Click to expand...

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.
Click to expand...
 
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
Click to expand...

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>
 
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?



John Vinson said:
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
Click to expand...

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>
Click to expand...
 
Back
Top