Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Separating mutliple data in one MS Access table column

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
jsawicki
Asked:
jsawicki
3 Solutions
 
Jerry MillerCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
hutchiesitCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jsawickiAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
yes, you are right
0
 
jsawickiAuthor Commented:
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
 
jsawickiAuthor Commented:
Nevermind, i got it figured out.  I just wasn't choosing the correct data type.  Thanks all
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now