Extracting digits from Date of Birth (1 Viewer)

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
Hi all

I am stuck on what should be a simple problem.
I am working with a database where the organisations unique ID for people starts with there date of birth in reverse order (yymmdd...)

In the flow of my form it is easiest to enter date of birth first.

In the after update event I would like to write some code to extract yymmdd from the Date of Birth already entered and then to set focus on the ID field after the entry of this data to allow completion of the unique ID.

I have tried:

Dim Yr2Dig As Integer, Mnth2Dig As Integer, Day2Dig As Integer

Yr2Dig = Format([DOB], "yy")
Mnth2Dig = Format([DOB], "mm")
Day2Dig = Format([DOB], "dd")

Where DOB is the DOB txt field.

When I debug this the variables just contain the digit 0

Any thoughts on what needs to be done?

cheers

Z
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
If you extract from a valid date/time value, Format function should work. Access stores date/time data as a double and default display is mm/dd/yyyy.

If you are storing DOB in a text field, likely the value is not a valid date/time. Use Left, Mid, Right functions.

Show example of DOB data.
 
Last edited:

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
Yes the DOB stores a valid date.

Currently I have DOB set to dd-mmm-yy format (i.e. 14-Jan-67)

I was hoping to use code that would not depend on the particular date format in the DOB text field so that the format can be freely altered to suit user needs.

The underlying access table obviously stores the integer of the date.

All this means that I don't see a way of using traditional Left, Right and Mid functions to do this.
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
Why do you say "Where DOB is the DOB txt field."

If DOB is a date/time field, what do you mean by 'txt field'?

Format setting does not change how data is actually stored. If DOB is a date/time field then Format function should work.

Cannot replicate the issue.

Why would you allow user to change table design?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:30
Joined
Sep 21, 2011
Messages
14,046
if it is date, just use

Code:
format(mydate,"yymmdd")

where mydate is your date control?

FWIW, I would not rely on that as if it is entered incorrectly, that is going to cause you problems when corrected, if you use that as the primary key.?

I would use the autonumber field for all the PKs and just have that as a display for users?

HTH

Edit: Your problem might be because you should use Me.DOB for a form/report? also do you have Option Explicit at the top of each module/form/report as that would pick up that issue.?
 

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
The identifier is not used as a primary key in my database - that is why it is not any sort of autonumber. I am just recording it for cross-compatibility with other systems.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 28, 2001
Messages
26,999
June's advice is good. If your DOB is entered already, the data type should be Date for her suggestions to work. If the DOB is actually just text, you can recover from this by trying CDate( Me.DOB ) - which will correct for the format differences.
 

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
Why do you say "Where DOB is the DOB txt field."

If DOB is a date/time field, what do you mean by 'txt field'?

Format setting does not change how data is actually stored. If DOB is a date/time field then Format function should work.

Cannot replicate the issue.

Why would you allow user to change table design?

Sorry when I say txt field I mean DOB is the txt box on my form that is bound to the underlying field Date of Birth which is a Date/Time field.
 

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
Why do you say "Where DOB is the DOB txt field."

If DOB is a date/time field, what do you mean by 'txt field'?

Format setting does not change how data is actually stored. If DOB is a date/time field then Format function should work.

Cannot replicate the issue.

Why would you allow user to change table design?

I am not allowing the user to change table design.
I have made the DOB format dd-mmm-yy because that is what appeals to my visual sense. If in implementation the users ask me to change the format to dd-mmmm-yy or yyyy-mmmm-dd etc I don't want to have to recode the database
 

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
After some experimentation I now have

Yr2Dig = Format(Me.DOB, "yy")
Mnth2Dig = Format(Me.DOB, "mm")
Day2Dig = Format(Me.DOB, "dd")

extracting the correct figures from the Date of Birth.

I now have the problem that

Format(me.DOB,"mm") produces just 6 for June whereas I need it to produce 06

I have a similar problem on single digit days
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:30
Joined
Sep 21, 2011
Messages
14,046
My way shows 180602 for today

I see no reason to make it harder?, though there will be a workaround. I seem to recall someone else had the same problem, but they were actually only after the month.

After some experimentation I now have

Yr2Dig = Format(Me.DOB, "yy")
Mnth2Dig = Format(Me.DOB, "mm")
Day2Dig = Format(Me.DOB, "dd")

extracting the correct figures from the Date of Birth.

I now have the problem that

Format(me.DOB,"mm") produces just 6 for June whereas I need it to produce 06

I have a similar problem on single digit days
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 28, 2001
Messages
26,999
Actually, my brain wasn't working right on my last post. Look at this link.

https://support.office.com/en-us/ar...n-access-47fbbdc1-52fa-416a-b8d5-ba24d881b698

Don't do this in three steps. Do it as

Code:
Y2M2D2 = Format( Me.DOB, "yymmdd" )

One call and you are done. If you get results at all from those other formats, then Me.DOB is in the right form. The above call will therefore do everything at once and will not "hiccup" over single-digit days or months (or years, for that matter.)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:30
Joined
May 21, 2018
Messages
8,463
Kind of strange that you are getting those results. Clearly, both mm and dd should have leading zeroes. That is the difference between m and d. Says that specifically in the help file
https://msdn.microsoft.com/en-us/vb...format-function-visual-basic-for-applications
dd Display the day as a number with a leading zero (01 - 31).
mm Display the month as a number with a leading zero (01 - 12). If m immediately follows h or hh, the minute rather than the month is displayed.
May be a regional setting issue.
 

zfar

New member
Local time
Today, 11:30
Joined
Aug 13, 2017
Messages
17
Actually, my brain wasn't working right on my last post. Look at this link.

Don't do this in three steps. Do it as

Code:
Y2M2D2 = Format( Me.DOB, "yymmdd" )

One call and you are done. If you get results at all from those other formats, then Me.DOB is in the right form. The above call will therefore do everything at once and will not "hiccup" over single-digit days or months (or years, for that matter.)

That got it solved - it was actually a problem of referencing the box on the form. Just adding the me.DOB to the code and your method sorted the issue. Thank you!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:30
Joined
Aug 30, 2003
Messages
36,118
Post 14 was moderated, I'm posting to trigger email notifications.
 

missinglinq

AWF VIP
Local time
Today, 14:30
Joined
Jun 20, 2003
Messages
6,423

Dim Yr2Dig As Integer, Mnth2Dig As Integer, Day2Dig As Integer

Yr2Dig = Format([DOB], "yy")
Mnth2Dig = Format([DOB], "mm")
Day2Dig = Format([DOB], "dd")

When I debug this the variables just contain the digit 0

Everything else aside, I think the reason that Variables all contain Zeros is that the Format Function, regardless of the Datatype of the Field being formatted, always returns a String...whereas the Variables are all defined as Integers.

Linq ;0)>
 
Last edited:

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
Nope. Works for me. Returns 18, 6 , 2.

However, declare them as strings and the leading zero will be retained.
 

Users who are viewing this thread

Top Bottom