Move data to the most left column

[curtyrut]

Hello, I am currently using ms access 2013 query function to provide a demographic file to an external vendors. I am struggling with compiling address information based on the 3rd party’s criteria. Unfortunately, the end users populating the source data is not following any particular format and therefore I have the following question for my access experts.

There are six different columns that contain address information yet is not consistently following any formats. All six columns are populated, or just five, four, three, two, or one column populated. How to move data from column three into column two only when column two is empty or any variation of such? Following that same logic until all columns are left justified so to speak. Essentially, move data to the left to fill the first blank column until all column are reviewed.

Thanks for your help.

 

[Ranman256]

run an update query,
set criteria
for col2 ,null
update col2 = col3

then run. this will move data from col 3 if col2 = null

then continue altering the query to fix what you need

 

[plog]

Unfortunately, the end users populating the source data is not following any particular format and therefore I have the following question for my access experts.

Why not? How is data getting into your system? Via forms? If so you can configure that form to force them to do that.

Or are you importing external files into tables? Is this an ongoing thing? Will new files constantly be imported? Explain the process a little more about how data gets in.

 

[curtyrut]

I am exporting the data from an external source and the inconsistency I think come from extracting from a global source (HRIS application). Thanks.

 

[MarkK]

I would always avoid moving data around inside your system.

I would 1) insert it correctly, which is what plog is going after, and I would 2) format it for use at retrieval time.

But I wouldn't 1) store it badly, then 2) process it inside your system (leaving you uncertain which rows have been processed, which not), and then still have to 3) format it properly at retrieval time.

 

[sneuberg]

I could write some code that would loop through the records of a table updating each record so that everything is shifted to the left. Would that suit your needs. Alternatively I could have the code create a new table with the fields left shifted and the original table left as is.

Let me know.

 

[jdraw]

I agree with Markk. Get it in the format you need to use it.
If the suppliers don't provide it in the proper format, and you have the ability to control that, then an option is to get a compliant supplier. However, in the real world, it happens --been there-- so capture what you've been supplied, and concoct a series of processes/routines to reformat to your needs.

Use some formatting routines, DO NOT adjust your major processing to adapt to each an every format supplied.

 

[curtyrut]

I could write some code that would loop through the records of a table updating each record so that everything is shifted to the left. Would that suit your needs. Alternatively I could have the code create a new table with the fields left shifted and the original table left as is.

Let me know.

Hello Sneuberg, yes providing structural coding to use will be very helpful. Thanks.

 

[sneuberg]

Here the code that changes the table in place. It's generic and will work on any numbers of columns. Just in case please back up you data before testing.

Public Sub ShiftFieldsLeft(strTableName)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim j As Long
Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName)
If rs.Fields.Count <= 1 Then
    Exit Sub
End If
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        For j = i + 1 To rs.Fields.Count - 1
            If IsNull(rs.Fields(i).Value) And Not IsNull(rs.Fields(j).Value) Then
                rs.Edit
                rs.Fields(i).Value = rs.Fields(j).Value
                rs.Fields(j).Value = Null
                rs.Update
            End If
        Next j
    Next i
    rs.MoveNext
Loop
rs.Close
db.Close


End Sub

This probably could be made more efficient if some forum member wants to give that a try.

 

[curtyrut]

Hello sneuberg, thanks for posting and excuse my ignorance due to my level of coding. After the code is populated in my database, what can I expect to see? Is there something I have to change in the coding? Thanks for being patient as I increase my level of coding.

 

[arnelgp]

another code:

Public Sub subShirtToLeft()
    Dim rs As DAO.Recordset
    Dim strContent As String
    Dim var As Variant
    Dim db As DAO.Database
    Dim i As Integer
    Dim varTest As Variant
    Dim arrColumns As Variant
    
    Const TABLE_NAME As String = "[COLOR=Blue]tblColumns[/COLOR]"
    Dim TOKEN As String
    TOKEN = Chr(255)
    ' must provide the names of columns here in order from left to right
    arrColumns = Array("[COLOR=Blue]col1[/COLOR]", "[COLOR=Blue]col2[/COLOR]", "[COLOR=Blue]col3[/COLOR]", "[COLOR=Blue]col4[/COLOR]", "[COLOR=Blue]col5[/COLOR]")
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(TABLE_NAME, dbOpenDynaset)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            strContent = ""
            For i = 0 To UBound(arrColumns)
                strContent = strContent & TOKEN & .Fields(arrColumns(i)).Value
            Next
            While InStr(strContent, TOKEN & TOKEN) > 0
                strContent = Replace(strContent, TOKEN & TOKEN, TOKEN)
            Wend
            If InStr(1, strContent, TOKEN) = 1 Then strContent = Mid(strContent, 2)
            If InStrRev(strContent, TOKEN) = Len(strContent) Then _
                strContent = left(strContent, Len(strContent) - 1)
            var = Split(strContent, TOKEN)
            On Error Resume Next
            .Edit
            Debug.Print strContent
            For i = 0 To UBound(arrColumns)
                varTest = var(i)
                .Fields(arrColumns(i)).Value = Null
                If Err.number = 0 Then
                    .Fields(arrColumns(i)).Value = varTest
                Else
                    Err.Clear
                End If
            Next
            .Update
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

replace the blue colored with correct tablename and field names (from left to right).

 

[curtyrut]

Hello Arnelgp and thank you for your contributions. However, I received the following error message after I posted and change the applicable fields to fit my need to a module.
Run time error '5':
Invalid procedure call or argument
on
strContent = Left(strContent, Len(strContent) - 1)

Thanks.

 

[sneuberg]

I'll assume you will run this procedure from a button on a form. In that case do the following after you have backed up your database.

1. Open the form where the button is to go in design view and add a command button. https://www.youtube.com/watch?v=cmT8z1Gh0AY but click cancel when the wizard comes up.

2. Right click on the button and click on properties. Change the Caption in the button's properties to whatever you want. I suggest giving it a meaningful name in the Other tab too.

3. Right click on the button and then click Build Event, choose code building and click ok. You should see something like:

Private Sub ShiftLeft_Click()

End Sub

Here it has the name ShiftLeft because I gave it that name in the properties.

4. Insert ShiftFieldsLeft "thetablename" in this sub like:

Private Sub ShiftLeft_Click()
  ShiftFieldsLeft "thetablename" 
End Sub

where thetablename would be the name of the table you want this done to.

5. Select and copy the code in my post and paste it below End Sub. You should end up with:

Private Sub ShiftLeft_Click()
  ShiftFieldsLeft "thetablename" 
End Sub

Public Sub ShiftFieldsLeft(strTableName)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim j As Long
Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName)
If rs.Fields.Count <= 1 Then
    Exit Sub
End If
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        For j = i + 1 To rs.Fields.Count - 1
            If IsNull(rs.Fields(i).Value) And Not IsNull(rs.Fields(j).Value) Then
                rs.Edit
                rs.Fields(i).Value = rs.Fields(j).Value
                rs.Fields(j).Value = Null
                rs.Update
            End If
        Next j
    Next i
    rs.MoveNext
Loop
rs.Close
db.Close


End Sub

Click Ctl S to save and close out of the code, return to form view.

 

[arnelgp]

im sorry, i cannot reproduce the error.
this line:
strContent = Left(strContent, Len(strContent) - 1)

comes from a continuation:

If InStrRev(strContent, TOKEN) = Len(strContent) Then strContent = Left(strContent, Len(strContent) - 1)

 

[sneuberg]

I don't get that error using arnelgp's code and it seems to work fine, but you should try my code. It only requires the table name, not the column names and besides I was here first.

 

[curtyrut]

sneuuberg, I received the following error:
Run-time error 3883
The calculated column contains an invalid expression:

If IsNull(rs.Fields(i).Value) And Not IsNull(rs.Fields(j).Value) Then

Thanks.

 

[sneuberg]

The code was intended for tables with text fields of the same size. I thought that's what you needed. I didn't anticipate calculated fields.

Please upload a database with at least the table we are dealing with so that I can see what I need to change. I would be helpful if the table were populated with some representative data.

 

[curtyrut]

im sorry, i cannot reproduce the error.
this line:
strContent = Left(strContent, Len(strContent) - 1)

comes from a continuation:

If InStrRev(strContent, TOKEN) = Len(strContent) Then strContent = Left(strContent, Len(strContent) - 1)

Arnelgp, finally I was able to get the code to run without errors. What was causing the previous error was the * Len(strContent)-1). After removing the -1, the code ran without any errors. I am currently checking the results to see what impact the -1 have on the results. Thank you.

 

[arnelgp]

here totally revised:

Public Sub subShirtToLeft()
    Dim rs As DAO.Recordset
    Dim strContent As String
    Dim var As Variant
    Dim db As DAO.Database
    Dim i As Integer, j As Integer
    Dim varTest As Variant
    Dim arrColumns As Variant
    Dim arrTemp As Variant
    Dim arrToSave() As Variant
    
    Const TABLE_NAME As String = "tblColumns"
    Dim TOKEN As String
    TOKEN = Chr(255)
    ' must provide the names of columns here in order from left to right
    arrColumns = Array("col1", "col2", "col3", "col4", "col5")
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(TABLE_NAME, dbOpenDynaset)
    ' resize arrToSave
    ReDim arrToSave(UBound(arrColumns))
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            ' initialise arrToSave
            For j = 0 To UBound(arrToSave)
                arrToSave(j) = Null
            Next
            strContent = ""
            For i = 0 To UBound(arrColumns)
                strContent = strContent & TOKEN & .Fields(arrColumns(i)).Value
            Next
            arrTemp = Split(strContent, TOKEN)
            ' save arrTemp to arrToSave
            j = 0
            For i = 0 To UBound(arrTemp)
                If Trim(arrTemp(i) & "") <> "" Then
                    arrToSave(j) = arrTemp(i)
                    j = j + 1
                End If
            Next
            While InStr(strContent, TOKEN & TOKEN) > 0
                strContent = Replace(strContent, TOKEN & TOKEN, TOKEN)
            Wend
            .Edit
            Debug.Print strContent
            For i = 0 To UBound(arrColumns)
                .Fields(arrColumns(i)).Value = arrToSave(i)
            Next
            .Update
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

same thing change the column names. must be from left to right.

 

[curtyrut]

Arnelgp, the previous code added special characters at the end of each address. Any insight on what is causing this? See below:
25 W Mystic AveÿMystic, CT 06355ÿY

The yY are the special characters.

Thanks.