MID Expression Help

[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"