updating an Access db column with a subset from another column in the same table

aueddonlineaueddonline Member Posts: 611
I have an Orders table in an Access 2003 database with two columns of interest:

Person & Person ID

Person is a lookup column for another table and shows a concatenation of 'firstName Surname ID', this is a text field

The Person Id field is one I have just created and is of type number.

For each record I would like to take the id from the Person field and populate the Person Id field with it.

What's another word for Thesaurus?


  • DatabaseHeadDatabaseHead Teradata Assc 16, Querying Microsoft SQL Server 2012/2014, CSM Member Posts: 2,730 ■■■■■■■■■■
    You can write an expression in the person id field to extract from the text. Try using a right, left expression, depending where the id is located. There are tricks you can do with LEN and other functions to isolate the ID. If that doesn't meet your needs you could look on a VBA forum for Access and they can assist.
  • aueddonlineaueddonline Member Posts: 611
    I am not sure that the right expression meets my needs as the ID length changes. So at minimum would have to use a regex I guess.

    I managed to solve changing the historic entries in the table by creating a function:
    Function GetNumber(text)
        Dim i As Integer, c As String
        If IsNull(text) Then GetNumber = Null: Exit Function
        For i = 1 To Len(text)
            c = Mid(text, i, 1)
            If c Like "#" Then
                GetNumber = GetNumber & c
            ElseIf Len(GetNumber) Then
                Exit Function
            End If
        Next i
    End Function

    Which I found here: https://www.experts-exchange.com/questions/27770841/Extract-a-number-for-a-string-field-in-ms-access-query.html

    And then using this in a query:
    UPDATE Orders SET Orders.[ID] = getNumber(Orders.[IDLookup]);

    My next issue is auto filling the the id field as rows are added to the table. I have looked at events on the form but don't seem to be able to directly use my function in an event. I understand I could otherwise auto fill with something like:
    Orders.ID = Orders.IDLookup.Column(0)
    What's another word for Thesaurus?
  • DatabaseHeadDatabaseHead Teradata Assc 16, Querying Microsoft SQL Server 2012/2014, CSM Member Posts: 2,730 ■■■■■■■■■■
    Without sample data it's really hard to assist, I am left to make up requirements that may or may not be accurate. Glad you found your solution, if you want additional help I would upload or post some dummy data.
  • aueddonlineaueddonline Member Posts: 611
    So just to update the problem I would like to solve. I have two columns in a table called Persons:

    Person ID - Number
    Person - Lookup (Text) which is a concatenation of 'forename+" "+surname+" "+Person ID' from another table

    The form used to populate Persons table only has the user enter the Person (Lookup) field. I would like to automatically populate the Person ID by using the data in this field without bothering the user.

    Typical values for the Person (lookup field) may be:

    Steve Farmer 45847825
    Alex Mack 103222
    What's another word for Thesaurus?
Sign In or Register to comment.