Solved

Data Array in MS-Access Database Field

Posted on 2014-03-18
5
1,435 Views
Last Modified: 2014-05-11
I am working with an older database and the programmer used a data field to place a data array and parse the data in different fields.    So for instance a text field with a 100 character with has information at different spaces.   For instance the field is laid out like this:

Spaces 1-10:  First Name
Spaces 10-20: Last name
Spaces 30-40:  Address
Space 40-50: Zip Code

So I need to put information from an excel spreadsheet into this table.   I would like to create a query to pull from this spreadsheet and input the data into the field in the table using the same array.   What syntax would I use to create this field using exact text spaces?
0
Comment
Question by:Earl Kelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39938650
upload a copy of the excel file.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39938671
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.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39939031
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

Open in new window

Then:

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

/gustav
0
 
LVL 1

Author Comment

by:Earl Kelly
ID: 39944220
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
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39944555
That's what my function does. Of course, as you have changed the fields, you must modify the function accordingly.

/gustav
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question