You are here:

Using MS Access/MS Access - Parse out field to separate elements

Advertisement


Question
I have this data.  Each line contains FileNameLocation, Size, CreationDate, ModifyDate.  
Field1
T:admCommon Filesdb1.mdb          92.0 KB (94208 bytes)          9/20/2008   9:54:28 PM  3/24/2011   10:41:20 AM
T:admCommon FilesHEICSIncident Commander HandbookIncid Cmdr contacts.mdb          1.50 MB (1572864 bytes)          11/6/2008   10:58:23 AM 2/4/2013    12:01:50 PM
T:admittingRESTOREDAdmittingPrompt Pay DiscountCopy of PROMPT PAY DISCOUNT ADJUSTMENT SHEET new.mdb     132.0 KB (135168 bytes)        1/20/2009   2:03:42 PM  6/3/2009    11:20:31 AM
T:admittingRESTOREDAdmittingQCQC Websites.mdb      96.0 KB (98304 bytes)          1/20/2009   2:03:51 PM  6/3/2009    11:20:54 AM
T:admittingRESTOREDAdmittingQCQC Websites1.mdb     96.0 KB (98304 bytes)          1/20/2009   2:03:51 PM  6/3/2009    11:20:54 AM
T:admittingRESTOREDAdmittingTeam LeadsAdmitting.mdb          17.88 MB (18747392 bytes)        5/20/2009   1:05:17 PM  6/3/2009    11:21:18 AM
T:admittingTeam LeadsAdmitting.mdb          21.20 MB (22224896 bytes)        11/10/2009  3:57:48 PM  3/11/2010   8:54:32 AM

I want to separate the fields out.  I got the first field but am stuck on the others.  

FileLocationName: Left([Field1],InStr(1,[Field1],"mdb")+2)

Please help me.  

Thank you,
Scott

Answer
Hi Scott,

You need to use the import feature of Access. Import the data using "External Data" menu item. Specify the source data as Access and follow the prompts.

If importing from Access did not work, then copy the access table into Excel and import from Excel. If that does not work, copy the table data as text in notepad, and import.

I hope this helps.

Mark  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mark Kaphle

Expertise

I can answer most questions on database design using Access. I can help with VBA too. I can integrate engineering and business needs in the database design.

Experience

I have more than 15 years of MS Access database design experience.

Education/Credentials
College degree in engineering. Graduate degrees in engineering and management. Current PHD student - concentration Industrial Engineering.

©2016 About.com. All rights reserved.