Pasting data from Excel in Access Datasheet Form with Line Breaks (1 Viewer)

[gstreichan]

Dear Experts,
I have been looking for a solution as there are lots of threads on the topic, but I can't figure out. I have a template in excel users fill out and several times they enter line breaks in excel field. When copying data from excel and pasting in a Datasheet form, the different words are entered together as:

Excel Field Access Field after pasting (result) How I want to update description in approveddesc field
Wison Thompson Wison ThompsonAvenue Third28900 Wison Thompson, Avenue Third, 28900.
Avenue Third
28900

It is ok to remain like this but what I want is the update query to update this description in another field "ApprovedDesc" as Wison Thompson, Avenue Third, 28900. Basically the description will be created in another field in same table with commas replacing the line breaks. In addition, when pasting a field from excel with line break to access table, it seems to have a space in the end of the description, which is not really a space as when I try deleting it, it deletes the last letter of description.

On update query I tried to update to in ApprovedDesc with Replace([UserDesc],Chr$(13) & Chr$(10), ", ") but it doesn't work, it works only if I do Alt+Enter in access itself but when description is pasted from excel as Access Field after pasting (result) it seems access doesn't read any line breaks there.

How do I get around of this, please?

 

[arnelgp]

you can try to create a user-defined function in a module:

Public Function fnCrLf2Comma(ByVal varText As Variant) As Variant
    Dim var As Variant
    If IsNull(varText) Then
        Exit Function
    End If
    fnCrLf2Comma = varText
    varText = Trim$(varText & "")
    If Len(varText) = 0 Then
        Exit Function
    End If
    var = Replace$(var, Chr$(13), "")
    var = Join(Split(varText, Chr$(10)), ", ")
    If Right$(var, 2) = ", " Then
        var = Left$(var, Len(var) - 2)
    End If
    fnCrLf2Comma = var
End Function

now on your Update Query:

"Update yourTableName Set ApprovedDesc = fnCrLf2Comma([ApprovedDesc]);"

 

[ebs17]

The goal in a relational database should be to break down the overall information into atomic information and store it in its own fields.

Sorting Addresses etc

We often get queries here as to how one can sort data like addresses where 110 Magnolia Drive comes before 2 Magnolia Drive, as the data is text. Just found this code here, that at least allows for a way around that and very easily to. :) Thank you Philip @sonic8...

www.access-programmers.co.uk

 

[arnelgp]

The goal in a relational database should be to break down the overall information into atomic information and store it in its own fields

like what? separate city, town, street number, street, villa, lot number, block number, country, planet?

 

[gstreichan]

you can try to create a user-defined function in a module:

Public Function fnCrLf2Comma(ByVal varText As Variant) As Variant
    Dim var As Variant
    If IsNull(varText) Then
        Exit Function
    End If
    fnCrLf2Comma = varText
    varText = Trim$(varText & "")
    If Len(varText) = 0 Then
        Exit Function
    End If
    var = Replace$(var, Chr$(13), "")
    var = Join(Split(varText, Chr$(10)), ", ")
    If Right$(var, 2) = ", " Then
        var = Left$(var, Len(var) - 2)
    End If
    fnCrLf2Comma = var
End Function

now on your Update Query:

"Update yourTableName Set ApprovedDesc = fnCrLf2Comma([ApprovedDesc]);"

Thank you so much! Worked beautifully!

 

[ebs17]

like what?

I'm a bit irritated that you have to explain the meaning and benefits of normalization to a non-newbie.

If you just want to view and print addresses, you can of course forego normalization steps.

For MANY addresses, the post office, for example, has an interest in delivering parcels in a more structured and planned manner.

 

[arnelgp]

ourse forego normalization steps

i don't see Address as candidate for normalization. as long as you can paste it on google map and show you that it is correct, that is fine.

 

[ebs17]

that is fine

Google certainly won't be bothered by different line breaks.