Extracting digits from Date of Birth

[zfar]

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]

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.

 

[zfar]

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]

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]

if it is date, just use

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]

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]

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]

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]

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]

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]

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]

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

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]

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]

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

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]

Post 14 was moderated, I'm posting to trigger email notifications.

 

[missinglinq]

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

 

[June7]

Nope. Works for me. Returns 18, 6 , 2.

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