Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Separating mutliple data in one MS Access table column

Posted on 2012-03-14
7
Medium Priority
?
310 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
[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
7 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 200 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 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 200 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

604 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