Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Break Imported Text Field from Microsoft Excel into Component Parts

    I have a Date/Time text field I am importing from a Microsoft Excel Worksheet. This field has several components I would like to break into separate fields – Field-01 Day of Week, Field-02 Text Name for Month, Field-03 Numeric Day of Month, Field-04 Numeric Value for Year, and Field-05 12-hour Clock Time.

    I would also like to create a Field-06 which would be Numeric Number for Month of the Year.

    I would like to do this using a Query, if possible. I do not know how to use Visual Basic. I envision six new columns in the query with a formula for each field to create the desired result.

    Any suggestions would be appreciated.

    Sample Date/Time Field
    Wednesday, November 30, 2022 1:58 PM



    I can do this in Microsoft Excel, but I would like to import the Worksheet without any changes, and then make these changes in Access.

    Thanks in advance for anyone’s help.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This sounds like it might be a waste of time if you have values that are full dates with time. By that, I mean if your table contains
    12/24/2022 01:00:30 PM
    then you can extract any of those components using the appropriate date functions in a query.

    If the query DayOfDate field contained this expression
    datepart("d","12/24/2022 01:00:30 PM") the result would be 24. Using m instead of d would get you 12. There are other date functions which you can use to get the text versions for day and month.

    https://www.techonthenet.com/access/functions/index.php
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    I am not sure my Access Table and the field I import from Microsoft Excel are values with full dates with time. I am attaching an example of the Excel Worksheet and a Access Table resulting from its import I was not able to use the date functions. Maybe I am doing something wrong.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    values that are full dates with time
    Your values are not date data type; they are strings in the format of Long Date. If there is a built in function that will convert those strings to date data (query or code) so that Date functions can be used on them I don't know of it. One likely can write a custom function for that, and I think regardless of whether or not this ends up with the values split into fields, that's probably going to be the only solution.

    I tried xl cell date and custom formatting but it remains a string, so no joy in fixing it at the source, it seems. I also tried various functions in your query to no avail. So if you can make use of a UDF (or as you say, vba) then this should be doable. Perhaps wait a bit and see if anyone else has a trick for doing it without code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Is the following the output you need?
    Attached Thumbnails Attached Thumbnails DateTime.PNG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    After import, can manipulate string like:

    CDate(Mid([Date Time Field], InStr([Date Time Field], ",") + 2))

    and

    Month(Mid([Date Time Field], InStr([Date Time Field],",") + 2))

    Surely your worksheet has more than one column of data?

    Don't see any way to avoid VBA if you want to save in table as a date/time type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thank You, this comes very close only need to break Part 3 into Month and Day of Month. I would be interested in knowing how you did this in Access.

    Maybe I need to make my changes in Excel, but I was trying to avoid that. I used to the Text to Columns command in Excel for column A to break it into 6 columns. I then created another column and used the formula =MONTH(DATEVALUE(C2&1)).

    I was trying to do something similar in Access without using Visual Basic.

    Thanks again for your response.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Calculations can be done in query or textbox or VBA.

    Can use other date functions on the part extracted by the Mid() function

    Query example:

    SELECT CDate(Mid([Date Time Field],InStr([Date Time Field],",")+2)) AS DT, Month([DT]) AS MoNum, Day([DT]) AS DayNum, MonthName([MoNum]) AS MoName
    FROM AccessForumQuestionTABLE;

    For MoName calc, could use string manipulation: Left([Date Time Field], InStr([Date Time Field],",")-1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's your data converted to a date with the MonthName in the 3rd column. VBA is required to get the second column, which are actual dates. If they were not, I don't think I could have used MonthName on the field values.
    Date Time Field Dte MnthName
    Friday, December 2, 2022 8:58 AM 12/02/2022 8:58:00 AM December
    Thursday, December 1, 2022 4:21 PM 12/01/2022 4:21:00 PM December
    Wednesday, November 30, 2022 1:58 PM 11/30/2022 1:58:00 PM November
    Tuesday, November 29, 2022 5:11 PM 11/29/2022 5:11:00 PM November
    Monday, November 28, 2022 4:06 PM 11/28/2022 4:06:00 PM November
    Monday, November 28, 2022 2:16 PM 11/28/2022 2:16:00 PM November
    Friday, November 25, 2022 3:42 PM 11/25/2022 3:42:00 PM November
    Wednesday, November 23, 2022 3:07 PM 11/23/2022 3:07:00 PM November
    Tuesday, November 22, 2022 4:59 PM 11/22/2022 4:59:00 PM November

    Stopping there for now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I calculated date without VBA in query. See post #8.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, nicely done. But you lost the time portion of date time field? I think I have the entire date time value as date-time. No idea if that matters in the end.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    To answer your question - yes, my worksheet has more than one column of data - however it is employee information.

    This is entry data for employees.

    I want to break the Date Time Field into component parts for query purposes.

    Thanks for your reply.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, I do not lose time portion with query calculation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    See the query for how I did this.
    There are also 2 Modules used for stripping out Text and Numbers
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Thank You June7 !!

    I was able to use the SELECT function for my first time to create Query01 in the attached database AccessForumQuestionTEST-DATABASE RevB.

    Note I added a couple of records to the original AccessForumQuestionTABLE to check the functionality of the fields I created.

    I was able to duplicate all of the fields I used to create in Microsoft Excel using Microsoft Access functions.

    However, one of the fields I couldn’t create was to convert the Short Time field in the query to MinutesOfDay, this is the last field in the query. What I am trying to do with that field was to take the hours to the left of the colon in the Short Time Field and multiply it times 60 and then add the number of minutes to the right of the colon. For example:

    Short Time 0:28 0 times 60 + 28 = 28 minutes

    Short Time 16:06 16 times 60 + 06 = 966 minutes

    This is the employee entry time in minutes of the day.

    Two other field which I would like to create but are not mandatory but would be nice to have.

    Year Month Day Text Format 2022-01-01

    Year Month Day Number Format 20220101

    Note in both of these fields if the month number and day number are single digits they must be formatted as mm and dd. I was able to do that in Excel but am trying to figure out how to do this in Access. These fields would be useful in reports.

    Thank you for your help.

    If you or anyone else can suggest how I might accomplish these last three steps it would be greatly appreciated.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-24-2018, 10:45 AM
  2. Replies: 3
    Last Post: 06-04-2017, 01:02 PM
  3. Replies: 6
    Last Post: 10-09-2014, 12:41 PM
  4. Replies: 2
    Last Post: 04-29-2014, 05:03 PM
  5. Replies: 1
    Last Post: 03-01-2013, 11:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums