Data Array in MS-Access Database Field

upload a copy of the excel file.

You can use the Space() function to pad the fields or you can redo the whole thing using a "no code" solution.  This seems very code intensive for no purpose.  You could link to the spreadsheet and export a fixed width text file.  You will need to go through the process once manually to make an export spec but there is no code involved.

You can use this function:

Public Function ArrayField(ByVal FirstName, ByVal LastName, ByVal Address, ByVal ZipCode) As String

    Dim strField    As String
    
    strField = Space(50)
    
    Mid(strField, 1) = Nz(FirstName)
    Mid(strField, 11) = Nz(LastName)
    Mid(strField, 31) = Nz(Address)
    Mid(strField, 41) = Nz(ZipCode)
    
    ArrayField = strField

End Function

Select all Open in new window

Then:

Select
    SomeField,
    AnotherField,
    ArrayField([First Name], [Last Name], [Address], [ZipCode]) As NameAddressField
From
    xlsYourLinkedExcelData

/gustav

Thank you guys for your help.  

I need to clarify my question.   I can get the information from the field using the Mid but I need to put the information back in the field.   Here is my question:

First Name:    Julie
Last Name:    Smith
DL#:     K8094000

And the output should be in one field in Access like this:

Combined Field:   Julie     Smith     K8094000

I could do spaces after, but this is going to vary based on the information in the field.  The field can be up to 10 characters long.  

Thanks in advance for your help