Generate Number

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I wish to be able to generate a number in the following
format by clicking a button on my form:

BLOGFX00211265

This is made up from the following:

BLOG (first 4 letters of surname)
FX (first letters of forename & 2nd intitial)
(or X if no 2nd initial)

00 (Zero,Zero)

211265 (d-o-b without slashes)

I would like this to be an editable field to allow for
changes, the number created here is only a best estimate.
E.g If we have twins, they may end up with the same
number, so therefore the 00 would be 01.

Hope this makes sense

Pete
 
Pete

One of the premises underlying good database design is that you don't put
more than one fact in one field. It sounds like you are trying to put 5
facts in one field. I'll hazard a guess that you are trying to come up with
a unique key by combining all these.

In Access, if you want to create a primary key that spans 5 columns, you can
do so without having to create a 6th column to hold the combination.

You can use concatenation to display the combination you are proposing by
way of a query, for use in forms and reports.
 
Hi Jeff,

I see what you mean, and your right I am trying to create
a primary key, but this is also the members registration
number that is required not only for Club purposes, but
Nationally too. There may just be 2 John Smiths with the
Same d-o-b's in the country, which is where the 00 comes
in.

What I want to do is save the person typing in the number
some time, If he clicks a button, this number will be
automatically generated for him, but will allow it to be
edited if the above case arises.

Thanks

Pete
 
Pete

There would be no reason you couldn't have your person entering data enter
LastName, FirstName, MiddleName, DOB, and have Access check for "duplicates"
on those fields, creating the appropriate "sequence number" (00, 01, 02,
....). Access forms can display "calculated" values that are not stored in
the tables -- that's where you'd show the concatenated number.

By the way, if you have more than one John L Smith, born 1/1/1951, are they
two different people, or the same person entered twice?!
 
Jeff,

The number I wish to generate is the one used to Rank our
members Nationally, this is not Local only to our Club. I
have no way of knowing what this members number is until
it is confirmed by the Governing body, then I can amend
what I generated.

All I am wanting to do is limit the amount of typing done
by the person inputting tha data, and generate most of
the number for him.

Peter
 
You can generate the number using a mid or left function to 'cut' the bits
out of the surname and first name fields and concatentate them together with
the DOB field.
something like:

Dim strMemberNo as String

'Generate the first part of the string
strMemberNo = left(form.txtsurname.Text, 4) & left(form.txtfirstname.Text, 1)
'Generate the second part of the string
if form.txtInitials.Text <> "" then
strMemberNo = strMemberNo & left(form.txtsurname.Text,1)
else
strMemberNo = strMemberNo & "X"
end if
'Add the number bit
strMemberNo = strMemberNo & "00"
'Finally add the date of birth

Something like that should work.
 
Back
Top