Return only the numbers contained in a field

[Brett]

Hello,

I would like some help please building a query that will return the
numbers only 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
field?

Thanks in advance,
Brett

 

[Jerry Whittle]

Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd
expect ugly, errors, and slow.

SELECT SalesOrder.RefNumber, PurchaseOrder.Memo
FROM SalesOrder, PurchaseOrder
WHERE PurchaseOrder.Memo Like "*" & [SalesOrder]![RefNumber] & "*";

In the above example a sales order of 34 would pull out the following.

"Sales Order 34:"
"Sales Order 343:"
"Sales Order 334:"
"Phone Number 555-3411"

You really need to get that data into a proper SalesOrder field and out of
the Memo.

 

[John W. Vinson]

Hello,

I would like some help please building a query that will return the
numbers only 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
field?

Thanks in advance,
Brett

Ow. You'll need some VBA code, and some luck. Might there be any OTHER numbers
(dates, phone numbers, addresses, etc.) in the memo field? Can you count on
there being a string "Sales Order" or not? Will there be multiple sales orders
in the memo field?

 

[Brett]

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

Here's an excerpt from the two tables:

PurchaseOrder table:

RefNumber Memo
2759 Sales Order 1700:
4724 Sales Order 79:
3500 Null
2000 Sales Order 50: 2 blocks of wood

SalesOrder table:

RefNumber
79
80
1700
etc.

The resulting query would give me the SalesOrder number and all the PO
numbers that have that SO number in their memo field.

Thanks again.

 

[John Spencer]

Try a query like the following

SELECT *
FROM PurchaseOrder, SalesOrder
WHERE SalesOrder.Memo Like "Sales Order " & PurchaseOrder.RefNumber & ":*"

That is going to be S L O W, but it should work fairly well.

In Design view
-- Add the two tables
-- Select the fields you want
-- Under SalesOrder.Memo enter the following as the criteria
Like "Sales Order " & PurchaseOrder.RefNumber & ":*"
--
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

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

Here's an excerpt from the two tables:

PurchaseOrder table:

RefNumber Memo
2759 Sales Order 1700:
4724 Sales Order 79:
3500 Null
2000 Sales Order 50: 2 blocks of wood

SalesOrder table:

RefNumber
79
80
1700
etc.

The resulting query would give me the SalesOrder number and all the PO
numbers that have that SO number in their memo field.

As an alternative to John S's suggestion:

SELECT PurchaseOrder.*, SalesOrder.*
FROM PuchaseOrder
INNER JOIN SalesOrder
ON SalesOrder.SalesOrderNumber = Val(Mid([Memo], 12))
WHERE [Memo] LIKE "Sales Order *";

or (MUCH less efficient but possibly required)

SELECT PurchaseOrder.*, SalesOrder.*
FROM PuchaseOrder, SalesOrder
WHERE [Memo] LIKE "Sales Order *"
AND SalesOrder.SalesOrderNumber = Val(Mid([Memo], 12));

 

[Brett]

This is what finally worked:

SELECT SalesOrder.RefNumber, PurchaseOrder.RefNumber
FROM SalesOrder, PurchaseOrder
WHERE (((PurchaseOrder.Memo) Like "Sales Order " & [SalesOrder].
[RefNumber] & ":"));