Help splitting a field into 2 fields in Access

Posted on 2013-09-23
Medium Priority
Last Modified: 2013-09-30
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"

Question by:Natspap
  • 3
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 39515565
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)

Assisted Solution

ggzfab earned 1000 total points
ID: 39516979
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...:

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.

Author Comment

ID: 39533121
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?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39533174
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];

Author Comment

ID: 39533202
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.

Author Comment

ID: 39533534
I finally got it to work Thanks everyone for the help!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

627 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