Bates Numbers

Access VBA code to work out range of numbers

There was a recent question on the litsupport group about how to make up a range of ‘bates numbers’ e.g.

In two fields you have:
DocID BatesNo
A00001 A00001
A00001 A00002
A00001 A00003
A00001 A00004

And what you actually want is one record stating:
DocID BatesNos
A00001 “A00001, A00002, A00003, A00004″

The only way to do this in Access would be to loop through the record set using VBA. this is a simple bit of code which I have adapted to the situation.

A simple function:

Function BatesRange(firstbates As String, lastbates As String) As String

Dim firstBatesNo As Long
Dim lastBatesNo As Long
Dim prefix As String
Dim i As Long

prefix = Left(firstbates, 1) ‘ gets prefix for bates numbers

firstBatesNo = CLng(Right(firstbates, 5)) ‘converts last 5 digits to a number
lastBatesNo = CLng(Right(lastbates, 5)) ‘converts last 5 digits to number

For i = firstBatesNo To lastBatesNo
BatesRangeTmp = BatesRangeTmp & prefix & Format(i, “00000″) & “;”
Next

BatesRange = BatesRangeTmp
End Function

All you do is pass it your two values for start and end. You can get this by grouping your data in a query by DocID, the displaying the first and last
values of the BatesNo using the standard [First] and [last] grouping functions in a group query. Hope this helps. If you have any questions or want the sample database I created, please email me.