Results 1 to 8 of 8
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Return only the numbers contained in a field

    Hello,

    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
    field?

    Thanks in advance,
    Brett

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  3. #3
    Join Date
    May 2007
    Posts
    3
    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:

    PurchaseOrder table:

    RefNumber Memo
    2759 Sales Order 1700:
    4724 Sales Order 79:
    3500 Null
    2000 Sales Order 50: added 2 more boxes

    SalesOrder table:

    RefNumber
    79
    80
    1700
    2000

    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.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    OMG.

    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2007
    Posts
    3
    I didn't design the database...it is in QuickBooks...i'm just trying to report off of their "bad design". Anyway, I got my answer from a usenet group. Thanks anyway.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Really!? Hahahahahahahahahaaaaaaaaaaaaaaaaaaaaa!

    Pro design there quickbooks! lol
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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:

    Code:
    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)
            
        End If
        
    End Function
    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:

    SalesOrderID:GetSalesOrder([MemoField])

    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.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by brettcbarry
    I didn't design the database...it is in QuickBooks...i'm just trying to report off of their "bad design". Anyway, I got my answer from a usenet group. Thanks anyway.
    Forum courtesy says that if you post the same question on multiple sites; when you get a solution you should link to that from your other threads so that future readers can benefit from the answer.

    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •