Help splitting a field into 2 fields in Access

I am trying to split my field "Shelving Date" in my "shelving" table into 2 separate fields called shelving date and shelving time.  How would I write a query that can execute this? The shelving date and shelving time are separated by a space.  Here is the format that the shelving date is in

2013-10-16 08:55:55

I would like to separate it into shelving date = "2013-10-16" and shelving time = "08:55:55"

Thanks!
NatspapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
running an update query

Update shelving
set [shelving_date]= left([Shelving Date],Instr([Shelving Date]," ")-1)
, [shelving_time]=mid([Shelving Date],Instr([Shelving Date]," ")+1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ggzfabCommented:
What's the datatype of the  "Shelving Date" field and the date and time field in your table?

When it's a date you can use the format statement to get the parts updated in separate fields like:

UPDATE shelving SET shelving.[JustDate] = Format([Shelving Date],"mm-dd-yyyy"), shelving.[JustTime] = Format([Shelving Date],"hh:nn:ss");

Open in new window


After this you can remove the original field, but I wouldn't do all this effort when the field is already defined as a Date datatype. The Format() function can be used when you need only a part of the date and having a date datatype field will make sure the OrderBy will work OK. When it's a text field this can cause trouble when there's zero suppression on the month or day...:
2013-1-12
2013-11-12
2013-2-12

So my advise is to use a date datatype for the shelcing date and the format() for getting the parts and to forget to split the field into two separate fields.
0
NatspapAuthor Commented:
I just changed the data type to Date/time and I believe that part is working now.  The only part i can not get working now is my macro.  I run my splitDate query and then my updateStudent query where i update this information into my STUDENT table.  Would this be the correct way to write my updateStudent table to update the data from shelving to student?

UPDATE STUDENT INNER JOIN Shelving ON STUDENT.[Order #] = Shelving.[Order #] SET STUDENT.[Shelving Date] = [Shelving].[Shelving Date].[Shelving Time];

Would there be any way to combine the SplitDate query and the updateStudent query?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rey Obrero (Capricorn1)Commented:
so you want to combine the shelving date and shelving time, is that correct ?

UPDATE STUDENT INNER JOIN Shelving ON STUDENT.[Order #] = Shelving.[Order #] SET STUDENT.[Shelving Date] = [Shelving].[Shelving Date] + [Shelving].[Shelving Time];
0
NatspapAuthor Commented:
I am trying to split the 'shelving date' into 'shelving date' and 'shelving time' from my 'shelving table' and then update my 'student' table with this information where the order numbers are equal.  My student table has all order numbers already and when the orders are shelved i am trying to update this table with the data that i import to the shelving table.
0
NatspapAuthor Commented:
I finally got it to work Thanks everyone for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.