Hi Team,
I'm Darshan Hiranandani, need some help formatting a cell to create lot numbers in the format: SupplyID-Date-Ascending number. I’ve managed to get the SupplyID and Date part working, but I’m struggling with the ascending number that should reset for each ingredient.
For example, I want the lot numbers to look like this:
- Ingredient 1: 001-241012-01, 001-241012-02, etc.
- Ingredient 2: 002-241012-01, 002-241012-02, etc.
Does anyone have suggestions on how to implement the ascending number for each ingredient without using a lot autonumber? Any advice would be appreciated!
Regards
Darshan Hiranandani
Don't create a new field for the Lot Number in this format. Create the LotNumber field as a number for the third segment, 01, 02, etc.
I assume this is supposed to be a field in a table, even though you referred to a "cell". Excel has cells; Access has fields in tables.
You can concatenate the segments of your proposed value from three components: SupplyID (formatted to display leading 0s), DateCreated (formatted as yymmdd) and a new field for the sequential LotNumber.
When you want to display the full concatenated value in a form, or on a report, do so in the query or in a calculated control.
Then approach the creation of the sequential LotNumbers in a VBA function or an expression, much like you'd create any other such sequential value.
I googled and found multiple examples. You can too.
It might look like this:
Private Sub CreateLotNumber()
Dim lngLotNumber as Long
With Me
lngLotNumber = DMax("LotNumber", tblYourTableNameGoesHere", "SupplyID = " & .txtSupplyID & " AND CreateDate = #" & .txtCreateDate & "#") _1
.txtLotNumber = lngLotNumber
End With
End Sub
There is some uncertainty about the "CreateDate", which is based on the fact that we don't have a good definition of how that date is defined in your business rules. I assumed for the purposes of this example, that it is based on the date on which you add the ingredient to "something".
