Calculating Job Number

[JudithJubilee]

Hi There,

I need to calculate a Job Number from a date.

I have a Booking Date and my client would like the BookingID to be made up
of the Week Number, the Initials of the Day and an Autonumber starting from
01 each time.

Examples

23/05/2008 - 21FR01
23/05/2008 - 21FR02

24/05/2008 - 21SA01
24/05/2008 - 21SA02
24/05/2008 - 21SA03

I'd appreciate your help. I can sort of figure out the 25SA but I don't know
where to begin to have the numbering restart at 1 on each day.

Thanks in advance

Judith

 

[Stefan Hoffmann]

hi Judith,

24/05/2008 - 21SA01
I'd appreciate your help. I can sort of figure out the 25SA but I don't know
where to begin to have the numbering restart at 1 on each day.

Something like this:

Dim JobID As String

JobID = Nz(DMax("JobID", "Jobs"), GetNewJobID())

Mid(JobID, 5, 2) = Right("00" + CInt(Mid(JobID, 5, 2) + 1), 2)

GetNewJobID() must return something like "21SA00".


mfG
--> stefan <--

 

[Klatuu]

Function CalcBookID(BookDate As Date) As String
Dim strBookId As string
Dim lngSeqNo As Long

strBookID = Format(DatePart("ww", BookDate),"00")
strBookID = strBookID & Left(WeekDayName(WeekDay(Bookdate)),2)
lngSeqNo = Nz(DMax("[BookingID]", "tblBooking", _
"Left([BookingID], 4) = """ & strBookID & """"),0) +1
CalcBookID = strBookID & Format(lngSeqNo, "00")
End Function

Change the field and table names to use your real names

 

[Ken Sheridan]

Judith:

Store only the final numeric element in the column as an integer number data
type, not the first four characters. These can be computed from the booking
date so to store them in a column introduces redundancy and the consequent
possibility of inconsistent data. So to get the next number in the
AfterUpdate event procedure of the Booking date control on the form put:

Dim strCriteria As String

strCriteria = "[BookingDate] = #" & Format(Me.[BookingDate],"mm/dd/yyyy") &
"#"

Me.[BookingID] = Nz(DMax("[BookingID]","[YourTable]", strCriteria),0)+1

To show the fully formatted ID in a computed control on a form or report use
the following as the ControlSource of the control:

= Format([BookingDate],"ww") & UCase(Left(Format([BookingDate],"ddd"),2)) &
Format(BookingID,"00")

You can use the same expression for a computed column in a query.

The BookingDate and BookingID columns together make up a candidate key for
the table, so can be used as the (composite) primary key, or at least should
be indexed uniquely if you are already using another column, e.g. an
autonumber column, as the key.

Using the DMax function to get the next number will work fine in a single
user environment, but in a multi-user environment on a network two or more
users would get the same the same number if adding records simultaneously.
Its particularly important therefore that the columns are uniquely indexed
so as to raise a trappable error if this occurs. The first user to save
their record would be successful the other(s) would trigger the error.

Ken Sheridan
Stafford, England