Concatenate Text field and date field

[DavisGail]

I currently have an Access 2003 database.

In a Student table, I have SSN field that stores the last four digits of
Social security number. I also have a DOB Date/Time field for Date of Birth.


I want to calculate my unique identifier in my form so that the "Identifier"
is SSN&DOB. (We originally had the full social security as the identifier,
but that is no longer allowed due to privacy restrictions).

My problem is that when I concatenate, I am getting, for example,
00041/30/1985 instead of 000431077. How do I show the date as a number
instead of a date format?

Is this possible?

 

[John Spencer]

Try using the expression
SSN & FORMAT(DOB,"mmddyyyy")

I would not store this information in the table since you already have
two fields that store the information.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[DavisGail]

John,
Thanks, That worked.

If I shouldn't store the information in the table, how do you suggest I have
this number as the new "unique identifier"? Any suggestions are greatly
appreciated.

 

[John W. Vinson]

If I shouldn't store the information in the table, how do you suggest I have
this number as the new "unique identifier"? Any suggestions are greatly
appreciated.

I'd suggest... don't. This number still violates student privacy (many
businesses use the birthdate for identification); it's not by any means
guaranteed to be unique (since your students will mostly be in the same age
group, shared birthdays are not at all unlikely, and four digits of SSN aren't
unique either); and the number provides neither mnemonic nor informative
benefits for the users.

Just use an autonumber, or a meaningless sequentially assigned number.

 

[John Spencer]

You can use multiple fields as the primary key field.

In DesignView Control+Click on both fields and then when they are both
selected, select Edit: Primary Key from the menu. That will make this
value the primary key for the table.

Personnally, I would use an autonumber as the primary key and make a
multi-field unique index based on the two items. Even then there is the
potential for duplicates arising based on the two fields. Not a high
potential but the last four of an SSN plus a DOB does mean there is the
possibility of a duplicate. If one does occur then you are going to use
some scheme to handle the duplication.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================