We help IT Professionals succeed at work.

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.
Comment
Watch Question

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
BRONZE EXPERT
Top Expert 2016
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])
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.

Author

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.
BRONZE EXPERT
Top Expert 2016

Commented:
yes, you are right

Author

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

Author

Commented:
Nevermind, i got it figured out.  I just wasn't choosing the correct data type.  Thanks all

Explore More ContentExplore courses, solutions, and other research materials related to this topic.