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

aueddonline · January 2017

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.

Bw

DatabaseHead · January 2017

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.

aueddonline · January 2017

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)

DatabaseHead · January 2017

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.

aueddonline · January 2017

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

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

Comments