Results 1 to 7 of 7
  1. #1
    GSPOwner1 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    3

    Creating a Date from a Text Field


    Is there a way to create a date from a a text field containing a series of numbers that make up our lot number system? For example, 121613CC-01 is made on December 16, 2013 and we are trying to build an expression that converts the 121613 portion to a Date of December 16, 2013. We can do it in MS Excel using =--TEXT(LEFT(CellRef, 6), "00-00-00"), but have been unable to do it in Access.
    Thanks,

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    yes there is

    one way is

    cdate(right([datefield], 2) & "/" & mid([datefield], 3, 2) & "/" & mid([datefield], 5, 2))

    then you can format it using the format command if you want something other than a mm/dd/yyyy format

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you want to convert it to a date, you can do it like this:
    Code:
    MyDate: DateSerial("20" & Mid([MyField],5,2),Left([MyField],2),Mid([MyField],3,2))
    Then you can apply the format of:
    mmmm dd", "yyyy
    on it to get it to look the way you want, while maintaining a Date data-type.

    You could do it like this also, but it would make it a Text data type (which could be problematic if trying to use it in calculations, sorting, searches, etc).
    Code:
    MyDate2: Format(DateSerial("20" & Mid([MyField],5,2),Left([MyField],2),Mid([MyField],3,2)),"mmmm dd, yyyy")

  4. #4
    GSPOwner1 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    3
    Quote Originally Posted by rpeare View Post
    yes there is

    one way is

    cdate(right([datefield], 2) & "/" & mid([datefield], 3, 2) & "/" & mid([datefield], 5, 2))

    then you can format it using the format command if you want something other than a mm/dd/yyyy format



    I had to change from cdate(right to cdate(mid and it worked perfectly. Thank you very much.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    oop that first part should have been LEFT() not RIGHT()

    no wonder I went to school with shoes on the wrong feet for years

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Here's another, more like the Excel formula:

    CDate(Format(Left([fieldname],6),"##/##/##"))
    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
    GSPOwner1 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Posts
    3
    Thank you JoeM. You have been a great help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-11-2013, 09:49 AM
  2. Autofill text field using rowsource+date
    By srcacuser in forum Forms
    Replies: 2
    Last Post: 11-30-2011, 09:44 AM
  3. Replies: 2
    Last Post: 09-25-2011, 08:52 AM
  4. Replies: 1
    Last Post: 08-07-2011, 07:58 AM
  5. Replies: 4
    Last Post: 01-25-2010, 04:14 PM

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