I would like to automate the Serial Number by cancatenating...

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

Guest

I posted this question on the Form Coding Discussion Group but got no
responce, so I will try in this discussion group.

I have a record with three fields; Serial Number, First Name, Last Name

Serial Number First Name Last Name
DR0001 Darrell Roak

My rule for the Serial Number is:

<first letter of First Name> <first letter of Last Name> <next sequential
number>

I would like to automate the Serial Number by extracting the first
characters of the First Name & and Last Name fields and concatenating them
with the next sequential number.

Is there a function defined for this or do I have to write a few lines of
code? If code needs to be written, could you help me a bit.
 
Possibly you didn't get any response because it's not a recommended thing to
do.

What you're trying to do is create a so-called "smart key", where that's not
a complimentary thing to call it! You're trying to store 3 separate pieces
of information in a single field, which violates relational database theory.

Have SerialNumber as an AutoNumber. You can then create a query that has a
computed field that returns what you're looking for: Serial: Left([First
Name], 1) & Left([Last Name], 1) & Format(SerialNumber, "0000"). Use the
query wherever you would otherwise have used the table.
 
What is the SPECIFIC need that you have to create a such a value? Are
you perhaps using it for something like a software registration key?

David H
 
Mr. Steele, Thank you:

I should have qualified my request by stating that I am quite the novice at
Access as well Data Base development. Your suggestion seems to be exactly
what I'm looking for. I do have one more question, (at this time).

If the "1" in this statement means the first character of the First Name,
would "2" mean the first two characters of the First Name, (<<< Left([First
Name], 1)>>>)?

Chances are I will have experimented with this by the time you respond.
Again, thank you for your response.

Darrell

David C. Holley said:
What is the SPECIFIC need that you have to create a such a value? Are
you perhaps using it for something like a software registration key?

David H
Possibly you didn't get any response because it's not a recommended thing to
do.

What you're trying to do is create a so-called "smart key", where that's not
a complimentary thing to call it! You're trying to store 3 separate pieces
of information in a single field, which violates relational database theory.

Have SerialNumber as an AutoNumber. You can then create a query that has a
computed field that returns what you're looking for: Serial: Left([First
Name], 1) & Left([Last Name], 1) & Format(SerialNumber, "0000"). Use the
query wherever you would otherwise have used the table.
Click to expand...
Click to expand...
 
Back
Top