Unanswered: Return only the numbers contained in a field
I would like some help please building a query that will return only the
numbers contained in a field:
Table "PurchaseOrder" has a "Memo" field that usually has the
following data: "Sales Order 7540:" or "Sales Order 34:".
How can I create a query that will show me all the Sales Orders and
their linked Purchase Orders (PurchaseOrder.RefNumber) where the
SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo
The only way I can think of to do it is to use a combination of the Left, Right, Mid, and InStr functions. Can you write VBA code?
It's a little hard to make suggestions without seeing a sampling of the data, but the approach you could take is use Instr to find where "Sales Order" appears in the string, then grab the string after "Sales Order" and convert the Sales Order to a number using the Val function.
There are a couple of issues I can think of:
1. Are there cases where there is more than one Sales Order?
2. Is it always spelled out Sales Order or do you see Sales Ord, or even Ord #.
3. From the wording of your question, you imply that there may not be a Sales Order number.
That does help somewhat. To shed more light on this...the
PurchaseOrder Memo field, if it has a sales order number, will always
begin with "Sales Order", then a space, then a string of numbers,
immediately followed by a colon, and then nothing else. Or this field
will be blank or could have some manually entered memo added manually
after the colon that does not contain "Sales Order".
I'm not very familiar with the InStr function and the Mid, Left and
Right functions, and I could look at the help for this...but do you
think you could provide an example of how I would do the query?
Here's an excerpt from the two tables:
2759 Sales Order 1700:
4724 Sales Order 79:
2000 Sales Order 50: added 2 more boxes
The resulting query would give me the SalesOrder number and all the PO
numbers that have that SO number in their memo field.
You do realise that this is shockingly bad design don't you?
You should have a related table for this, not just a memo field. Then it would be dead easy. My guess is that it's way too late for that though.
Anyway, yeah, I would create a "ExtractNumbers()" kind of function and use it to spit out the numbers into a temporary table. Then relate the temp table to your orders to get the details of them. Very messy, but it should work.
I am curious, what advice did you get? My approach would be to create a procedure to parse the data. Then call the procedure from the query. That way you can add comments and make the code a little more maintainable. Here is an example:
Public Function GetSalesOrder(varData As Variant) As Long
Dim strData As String
Dim strEndOfMemo As String
Dim lngPositionOfSalesOrderID As Long
Dim lngPositionOfFirstColon As Long
GetSalesOrder = 0
'Convert any Null values to an empty string
strData = Nz(varData, "")
'If not zero then it found Sales Order in the memo field
lngPositionOfSalesOrderID = InStr(1, strData, "Sales Order")
If lngPositionOfSalesOrderID <> 0 Then
strEndOfMemo = Mid(strData, lngPositionOfSalesOrderID + 12, 99999)
'Then you could use Val to get the number
GetSalesOrder = Val(strEndOfMemo)
Paste this code into a new module and save the module, then in your query (in the query design editor) add a field like this:
Replace MemoField with the actual name of the memo field. Then in another query you should be able to link the SalesOrderID to the Sales Order. Keep in mind, my code returns a zero if there is no Sales Order ID in the memo field.