X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Extracting Numbers From A String?, Access 2010    
post Mar 13 2018, 10:56 PM

Posts: 6
Joined: 13-March 18

Background: Need to compare two *similar but different* data tables from two separate databases to find differences from field to field. A regular JOIN query would do the trick, except TableB is stupid.

The important fields for the query are:

TableA: PositionCode(8 digit number)
TableB: Title (text), Organization ID (text, sometimes null)

The problem: TableB doesn't contain a field that corresponds to the PositionCode field in TableA . . . BUT, it's Title field can contain the number. Here are a couple of example records:

Also, sometimes the Title field doesn't contain a Position number, but the Organization ID field sometimes contains a number (it also sometimes contains alpha characters that don't seem to relate to anything). Sometimes there is no information in any field in TableB to connect to a corresponding record in TableA, and I need to know that, too. Likewise, I need to know when there are records in TableA that have no matching record in TableB.

So, I've exhausted my working knowledge of string manipulation to figure out how to consistently extract the 8-digit positioncode from the Title field in TableB. After that, how do I tell my query to also look at the Organization ID field when comparing TableA to TableB?

The project I'm working on is classified, but I will put together a dummy version (with a lot fewer records) that I can share if that will make this conversation easier! Thank you in advance for talking me through this.

Go to the top of the page
post Mar 13 2018, 11:08 PM

Posts: 6
Joined: 13-March 18

Correction to say: PositionCode in TableA is actually a text field to retain the leading zeros.
Go to the top of the page
John Vinson
post Mar 14 2018, 12:36 AM

UtterAccess VIP
Posts: 4,198
Joined: 6-January 07
From: Parma, Idaho, US

So the 8-digit Position Number might be in Title, or might be in OrganizationID, or might be in neither? yuck!

To find the "mights" you will need to use a (horribly inefficient) Cartesian join query, comparing every record in TableA to every record in TableB, on BOTH fields:

SELECT TableA.*, TableB.*
FROM TableA, TableB
WHERE (TableB.Title LIKE "*" & [TableA].[Position Number] & "*") OR (TableB.OrganizationID LIKE "*" & [TableA].[Position Number] & "*");

This will not work if (as I suspect) [Position Number] sometimes contains excess garbage (e.g. 12345678A will NOT records containing 12345678 without the A).

To find the records with no match in either field, you'll probably need to use the even WORSE query:

SELECT TableA.[Position Number]
FROM TableA WHERE NOT EXISTS (SELECT TableA.[Position Number]
FROM TableA, TableB
WHERE TableB.Title LIKE "*" & [TableA].[Position Number] & "*") OR (TableB.OrganizationID LIKE "*" & [TableA].[Position Number] & "*"));

John W. Vinson
Wysard of Information
Go to the top of the page
post Mar 14 2018, 05:31 AM

UtterAccess VIP
Posts: 9,190
Joined: 10-February 04
From: South Charleston, WV

Can you show both values that you want to match? I was thinking of joining with LIKE.

Robert Crouser

My company's website
Go to the top of the page
post Mar 14 2018, 08:52 AM

UtterAccess Editor
Posts: 18,208
Joined: 29-March 05
From: Wisconsin


Okay, so you need to find eight consecutive digits in TableB.Title - if there is one. If you can, you should add a new text field to TableB to store the eight-digit value that you extract, because extracting it is going to slow down any query that does so, especially if TableB has a lot of records in it. Here's a function you can use to pull the values, where they can be found:

Public Function ExtractedEightDigits(ByVal varInput As Variant) As String

    Dim i As Integer, j As Integer, intConsecutiveDigits As Integer
    If IsNull(varInput) Then Exit Function
'   Find a digit
    For i = 1 To Len(varInput)
        If IsNumeric(Mid(varInput, i, 1)) Then
'           Found a digit.  See if the next seven characters are also digits.
            intConsecutiveDigits = 0
'           Loop from the found digit to the end of the string
            For j = i To Len(varInput)
                If IsNumeric(Mid(varInput, j, 1)) Then
'                   Count how many consecutive digits there are
                    intConsecutiveDigits = intConsecutiveDigits + 1
'                   Stop counting when when we find a non-digit character
                    Exit For
                End If
            Next j
'           If we found exactly 8 digits in a row, return that value and exit
            If intConsecutiveDigits = 8 Then
                ExtractedEightDigits = Mid(varInput, i, 8)
                Exit Function
            End If
        End If
    Next i

End Function

You can use this code either in a Select query (which will be slow), or you can use it with an Update query that populates a new text field with the extracted values, which should make things easier, as long as you don't need to deal with new records in TableB that don't have the code in a separate field.

Hope this helps,

This post has been edited by doctor9: Mar 14 2018, 09:13 AM
Reason for edit: Added comments to my code

(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
post Mar 14 2018, 09:59 AM

Posts: 6
Joined: 13-March 18

Thank you! This was my original plan of attack, but I couldn't figure out how to write the function to extract the Position Code correctly. I will give this a try.
This post has been edited by casras2006: Mar 14 2018, 10:08 AM
Go to the top of the page
post Mar 14 2018, 10:06 AM

Posts: 6
Joined: 13-March 18

Thank you! This seems so simple. I'm telecommuting today and don't have access to my project, but I plugged this solution into a little dummy project and it worked beautifully. The real thing has 10k+ records in each table -- I'm guessing that will really screw with performance?

I'm still interested in extracting the PositionCode from the Title in TableB so users can at least see it side by side the TableA PositionCode. . . maybe use the custom function in Dennis' solution below in a calculated field in the query that I can then display in a report?

Going to post my dummy project so you all can look and comment, if you're interested in following up. I very much appreciate your help. I'm constantly learning new lessons from the UtterAccess forums, but this is my first time posting -- wasn't sure what to expect, but am blown away by how immediate and useful the help was!
This post has been edited by casras2006: Mar 14 2018, 10:08 AM
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th March 2018 - 10:15 AM