How do you do Alpha sequential “numbering”?

Question

I am working on a project where I need to do sequential-alpha (is that even a term?).

Example:

130725A

130725B

130725C

Where the first six characters are today’s date and the last is sequential-alpha.  Starts over again the next day.

 I plan to keep a table as a log (tblBatchIDs (fields: BatchDate and BatchIDMod)) of so I will be able to use DMax.

I have figured out a way to do this by using the following two fields in my query, which looks up the corresponding Letter and it works just fine.

ModNum: Nz(DMax("[BatchIDModifier]","tblBatchIDs","[BatchDate] = " & Format(Now(),"yyyymmdd")),"0")+1

Pos34: DLookUp("[Alpha]","tblNumtoAlpha","[NumID] =" & [ModNum])

This is a learning question, is there a way to do Sequential-Alpha other than via the lookup table?

As always, thanks for your help.

Answer 1

First, you should use two fields in the table for that, one for the date and the other for the sequential numbering.  It is much easier to manipulate (do what you want) things when they are separate than it is to take a combined value apart.  OTOH, it is very easy to put the separate values together (concatenate) whenever you need to display them to users in a form or report.  This way you so not need an extra table.

Rather than store letters in the sequential numbering field, I would make it an Integer field so calculating the next number is easy.  It's also fairly simple to convert a number to a letter using the Chr function.

If you go this way, the VBA code in the form's BeforeUpdate event procedure to calculate the next number and put it in the sequential number field could look like:

   Me.BatchDate = Date

   Me.ModNum= Nz(DMax("ModNum", "BatchesTable", "BatchDate = Date()", 0) + 1

And the form/report text box expression that converts the ModNum field's integer value to one or two letters could look like:

   =Format(BatchDate, "yyyymmdd") & IIf(ModNum > 26, Chr(64 + (ModNum - 1) \ 26), "") & Chr(65 + (ModNum - 1) MOD 26)

If I understood what you want, I think that should be all you need.

Answer 2

This is a learning question, is there a way to do Sequential-Alpha other than via the lookup table?

Yes, you can look it up directly from the operational table.  If we assume this is named Batches and the key column containing the values in the format 130725A etc is named BatchCode, the expression would be:

Format(VBA.Date,"yymmdd") & Chr(Nz(DMax("Asc(Right(BatchCode,1)),","Batches","Left(BatchCode,6) = """ & Format(VBA.Date,"yymmdd") & """"),64)+1)

Usually when incrementing a value in a key column like this it is done in the BeforeInsert event procedure of the data entry form, assigning the value returned by the expression to the control bound to the BatchCode column in the form.

In a multi-user environment there is the risk of the same value being obtained more than once before the row is saved to the table, so the key violation error should be trapped in the form's Error event procedure and in the event of the error the value should be assigned again to the control, using the same expression.

Of course, if you are also including the date in another column in the table, you should not incorporate the date in the structured key as this introduces redundancy and the risk of update anomalies, but simply store the suffix letter in a column.  The combination of the date column and the suffix letter column constitute a candidate key, so can either be made the composite primary key of the table, or if a separate surrogate key such as an autonumber is used, be included in a single unique index.

Answer 3

John,

Very good point.  I am sure they won't do more than a couple of batches each day, but just in case I will disable the button to create batches if there have already been 26 created that day.

Thanks,

Nancy

Answer 4

Just don't forget the "On Beyond Zebra" issue that Dr. Seuss addressed so eloquently:

 http://www.amazon.com/dp/0394800842/?tag=googhydr-20&hvadid=2915484865&hvpos=1t1&hvexid=&hvnetw=g&hvrand=5430689291177462062&hvpone=&hvptwo=&hvqmt=e&hvdev=c&ref=pd_sl_383c7e3shv_e)

Otherwise you may end up with batch numbers like 130828[, 130828^ and - worse, since Access is not case sensitive - 130828a and 130828b.

Are you QUITE CERTAIN that you will never have over 26 batches in a day at any time over the lifespan of this database?

Answer 5

Ken & Marshall,

Thank you both for your answers.  They seem to be very similar and I will be able to use them in my programing.  The key is the Chr() function.

Thanks again,

Nancy