auto numeric alpha field

  • Thread starter Interactive worksheet
  • Start date
I

Interactive worksheet

Hello,

I'm creating a database and i want to mix letters and numbers as a key that
auto genarates, i was wondering if that's possible and if yes, how?

Please help.

Thanks in advance,

Alex
 
J

Jeff Boyce

Alex

A common "rule" in effective database design is "one fact, one field". It
sounds like you are trying to put multiple facts into one ("key") field.

Since Access can treat a combination of fields as a primary key, why not
store the individual pieces in separate fields, and tell Access to treat the
combination as unique and the primary key?

Access offers an "Autonumber" data type that "automatically generates".
However, it is intended as a unique row identifier, is not guaranteed to be
sequential, and is generally unfit for human consumption.

You may need to "roll your own" (create your own procedure to generate) your
key field(s) values -- check on-line for "Custom Autonumber" and "Custom
Sequence Number" to get an idea of how to do this.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
I

Interactive worksheet

Just to clarify, there arent any auto generate fields that for example are
123A?
 
B

BruceM

Custom sequences of all sorts can be generated, or you can combine fields as
Jeff suggested, but in any case you need to define the logic behind the
sequence. What follows 123A? Where does the "A" come from? Does the
sequence or any portion of it restart? If so, under what circumstances?
 
I

Interactive worksheet

I want it all system generated. For instead i have:

123A go all the way to
999A in which case the next number will be
001B, is this doable in access and if yes, how?

Thanks a lot in advance

Alex
 
B

BruceM

It is possible, but with the long weekend coming up the best I can do is to
suggeest a search as Jeff suggested. I played around with it a bit, but
couldn't quite work out everything. I can take another look next week, but
I don't intend to go near Access for the next few days.
 
B

Bob Quintal

=?Utf-8?B?SW50ZXJhY3RpdmUgd29ya3NoZWV0?=
Just to clarify, there arent any auto generate fields that for
example are 123A?
Click to expand...

No, you have to write a visual basic function.
Do you want
123A, 123B, 123C or 123A, 124A, 125A?
What happens when you hit Z? AA or ?
Do you want to exclude I,O,Q,X?

You will need to answer all of the above questions before anyone can
suggest how to write the function.
 
I

Interactive worksheet

Hello, wow, lots of responses, thanks a lot for taking an interest.

This is what i want.

if i started out with 001A and went to 999A the next thing in sequence will
be 001B go all the way through to 999B to 001C so on and so forth. It will
be a long time until it goes through an alphabet, long enough for me not to
worry about it at all. Can you make that in VBA?

Please let me know and again, thanks a lot.

Alex
 
B

BruceM

The easiest way may be to use something like this in the form's Current
event. The code assumes that the number is stored in a Number field named
SequenceID in the table tblSeq. Substitute your actual field and table
names.

Private Sub Form_Current()

Dim lngSequence As Long
' For the first record only, Nz is needed
lngSequence = Nz(DMax("[SequenceID]", "tblSeq"), 65000)

If Me.NewRecord Then
If Right(lngSequence, 3) = 999 Then
Me.SequenceID = lngSequence + 2
Else
Me.SequenceID = lngSequence + 1
End If
End If

End Sub

The first number will be 65001, the next 65002, etc.

Make a query based on the table. Add a calculated field (give it whatever
name you like):
SeqFormat: Right([SequenceID],3) & Chr(Left([SequenceID],2))

Switch to the query datasheet view to see how it looks. Here's how it
works:
Right([SequenceID],3) takes the rightmost three characters from SequenceID
(001 in the case of the first number). Chr(Left([SequenceID],2)) applies
the Chr function to the leftmost 2 digits from SequenceID (65 for the first
999 records). The Chr function returns the character that is associated
with the number 65 in the ASCII character set, which is "A".

Back to the VBA code, if the rightmost three digits in the highest
SequenceID are 999, the code adds two instead of 1, so that 66000 is
skipped. The next number is 66001. Chr(66) is B, and the formatted
sequence begins again at 001B.

Base a form on the query, and bind a text box to the SeqFormat field in
order to see the number displayed in the format you prefer.

Remember that the format of the stored number is not important. There is
probably a way to store the number as 001A, 002A, etc., but it would be
considerably more complex to work out the code, with no difference to the
end user.
 
B

Bob Quintal

=?Utf-8?B?SW50ZXJhY3RpdmUgd29ya3NoZWV0?=
Hello, wow, lots of responses, thanks a lot for taking an
interest.

This is what i want.

if i started out with 001A and went to 999A the next thing in
sequence will be 001B go all the way through to 999B to 001C so on
and so forth. It will be a long time until it goes through an
alphabet, long enough for me not to worry about it at all. Can
you make that in VBA?

Please let me know and again, thanks a lot.

Alex
Click to expand...



Here's a little function that creates an increment of field myID in
table MyTable. You need to add error handling, and customize to your
situation.

public function fNextAlfaID().
dim CurrentID as variant
dim numpart as integer
dim alfaPart as integer

CurrentID = Dmax("myID","MyTable")
If len(currentID) <> 4 then
'bad number. Handle as you see fit
else
numpart = cint(left(CurrentID,3)
alfapart = asc(mid(currentID,4,1))
if numpart < 999 then
numpart = numpart +1
fNextAlfaID = format(numpart,"999" & chr(alfapart)
else
if alfapart < asc("Z") then
numpart = 0
alfapart = alfapart + 1
fnextalfaID = format(numpart,"999" & chr(alfapart)
else
'out of range, handle as you please.
end if
end if

end if
end sub
 
Top