how can I store short dates numbers only no slashes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am triing to concatenate parts of cells to create ID numbers but the Date
of Birth stores slashes and puts them into my ID numbers
 
See VBA Help on the Month, Day, and Year functions. They return variants
(integers) representing the component part of the date. If YourDate =
12/13/2005

Month(YourDate) returns 12.

Sprinks
 
eleeiv said:
I am triing to concatenate parts of cells to create ID numbers but the Date
of Birth stores slashes and puts them into my ID numbers
Click to expand...

The slashes aren't being stored, they are part of the display (based on
regional settings)

If you're trying to extract individual parts of the date to use in a
string ID or long ID, you're going to need to use the DatePart function

Example:
Dim intMonth as Integer
Dim intDay as Integer
Dim intYear as Integer
Dim strNewID as String

intMonth = DatePart("m", [YourBirthdayField])
intDay = DatePart("d", [YourBirthdayField])
intYear = DatePart("yyyy", [YourBirthdayField])
strNewID = "ID" & CStr(intMonth) & CStr(intDay) & Cstr(intYear)
' The result of strNewID would be 'ID04272006' if you ran this today
 
I am triing to concatenate parts of cells to create ID numbers but the Date
of Birth stores slashes and puts them into my ID numbers
Click to expand...

I'd step back and reconsider. Birthdates are not unique; many people
were born on May 16, 1946. Quite a few of them probably have the
initials JWV. Building an "Intelligent Key" such as jwv05161946 will
be possible, but a fair bit of work - and you have NO guarantee that
it will be unique or appropriate!

As a rule, one should NOT store multiple pieces of information in one
field. It's just a bad idea for many reasons.

John W. Vinson[MVP]
 
Back
Top