MID Expression Help (1 Viewer)

[randallst]

Hi there,

I’m sorry if this has already been asked but I’ve been struggling to know what to search. I want to separate out parts of a Lot Batch Number in my database.

I have a field called ‘Lot Batch Number’ and their styles are as follows;
O123456-1-0-00001
O123456-12-0-00001

I want to create two fields that capture the Shop Order and the Release Number.

Shop Order = O123456
Release = 1 (can go up to 100)
Sequence = 0
Specimen = 00001

I have created an LEFT expression to capture the O123456 of the Lot Batch Number, but I need an expression to capture the ‘Release’ part of the Lot Batch Number (highlighted in BOLD above). It’s not as simple as doing a basic MID as it can be 1 to 3 characters long.

Hopefully what I’ve explained makes sense and hopefully someone might be able to help me

Kind Regards
Stuart

 

[Galaxiom]

This is backwards. You should have separate fields for ShopOrder and RelaeaseNumber. LotBatchNumber should be calculated from these.

If you have no control over LotBatchNumber your requrement is:

Mid(LotBatchNunber, 9, Instr(9, LotBatchNumber, "-") - 9)

 

[randallst]

This is backwards. You should have separate fields for ShopOrder and RelaeaseNumber. LotBatchNumber should be calculated from these.

If you have no control over LotBatchNumber your requrement is:

Mid(LotBatchNunber, 9, Instr(9, LotBatchNumber, "-") - 9)

You my friend are a lifesaver! I like to research myself and try and learn myself before caving in, but this was doing my head in aha! Thank you

 

[randallst]

Would you know if its possible to use the results from the LEFT and MID expressions as a filter of a query?

When I try to do a simple filter, it errors stating;
"syntax error (missing operator) in query expression 'Shop Order'"

 

[arnelgp]

make a function so that it will not be hardcoded.
copy and paste to standard module.

to get the ShopOrder:
=getLotBatchPart([Lot Batch Number], BatchPart.ShopOrder)

to get the Release:
=getLotBatchPart([Lot Batch Number], BatchPart.Release)

to get the Sequence:
=getLotBatchPart([Lot Batch Number], BatchPart.Sequence)

to get the Specimen:
=getLotBatchPart([Lot Batch Number], BatchPart.Specimen)

to call from query:

SELECT getLotBatchPart([Lot Batch Number], 0) AS ShopOrder,
getLotBatchPart([Lot Batch Number], 1) As Release,
getLotBatchPart([Lot Batch Number], 2) As Sequence,
getLotBatchPart([Lot Batch Number], 3) As Specimen From yourTable;

Public Enum BatchPart
    ShopOrder
    Release
    Sequence
    Specimen
End Enum

Public Function getLotBatchPart(ByVal LotBatchNo As String, ByVal Part As BatchPart) As String
    getLotBatchPart = Split(LotBatchNo, "-")(Part)
End Function

 

[arnelgp]

on the function, the second parameter is zero based:
0 = ShopOrder
1 = Release
2 = Sequence
3 = Specimen

you can use the function in query, to filter by ShopOrder:

SELECT *
FROM yourTable Where getLotBatchPart([Lot Batch Number], 0) = "0123456"

 

[Pat Hartman]

I know that people like to make "meaningful" identifiers but it is far better to use an autonumber as the PK and to store the separate parts of the "meaningful identifier" as individual fields rather than mushing them into a single field. You can always concatenate them together for display on reports but you will find working with them ever so much easier if you don't mush them. You would also create a unique index on the combination of the four fields to enforce uniqueness.

It may be "too late" for you to fix the problem in this application permanently by separating the data into atomic fields but do keep it in mind for future reference.

"too late" is in quotes because it is actually never too late to fix a problem. The more time a design flaw exists, the more kludges you have to build to work around it so the sooner you fix the root of the problem, the sooner you can stop building kludges.