Solved

Separating mutliple data in one MS Access table column

Posted on 2012-03-14
7
302 Views
Last Modified: 2012-03-15
I imported an excel spreadsheet into Access and want to know how to separate data from one column into two columns.  The column is [Last Inventory Software Scan] and the data it stores is date in format(mm/dd/yyyy) and time in format(hh:mm am/pm).  The time isn't required and don't know how to separate it or should i separate it within excel before importing?  If best to separate in excel, what are those steps.
0
Comment
Question by:jsawicki
7 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 50 total points
ID: 37722736
The easiest way would be a 'Text to Columns' in Excel before importing it. I am sure that there is a delimiter or it is a fixed width. Anything in Access would be much harder.

http://support.microsoft.com/kb/214261
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 100 total points
ID: 37722775
you can import the excel file, and after importing you can separate the date from the time with

datevalue([Last Inventory Software Scan])  

to get the time, use

timevalue([Last Inventory Software Scan])
0
 

Assisted Solution

by:hutchiesit
hutchiesit earned 50 total points
ID: 37722979
Best to do the separation in Excel.
One way would be text to column : On the Data Menu or Ribbon, select Text to Column and follow the instructions.

However, if in excel the data is currently classifed as a Date/time format you could change the format to a simpler date format such as dd/mm/yyyy and excel will change the data for you.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:jsawicki
ID: 37723093
I will try these suggestions tomorrow at work and let you know how it goes.  

Capricorn1:  for your suggestion, i assume this will be written in the query field.

Thanks.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37723096
yes, you are right
0
 

Author Comment

by:jsawicki
ID: 37725098
The date and time values worked perfectly. thanks Capricorn

For the text to columns in excel, for some reason when i used the wizard, it separated the the time into a different column, however, it zero'd out the time in the orginally column so i was left with the data below in the date column.  When the cell was clicked on, it displayed as 2/24/2012 12:00 AM.  Did i miss a critical step within the wizard to remove the data when splitting the cells or is that just what i would have to live with if i choose this path in excel?  Since that is a useful tool, i wanted to see if i am doing something wrong before giving Capricorn sole solution credit.  Thanks all for your time


2/24/2012 0:00      9:39:00 AM
2/29/2012 0:00      5:02:00 AM
2/29/2012 0:00      5:02:00 AM
2/29/2012 0:00      8:23:00 AM
2/24/2012 0:00      9:05:00 AM
0
 

Author Comment

by:jsawicki
ID: 37725228
Nevermind, i got it figured out.  I just wasn't choosing the correct data type.  Thanks all
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now