Results 1 to 7 of 7
  1. #1
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67

    Conversion Of Text Date/Time to Date and Time

    I have imported a text file into Access that has a field that lists date/time as:



    1/4/2011 15:42

    In Access, I want to convert this to a date AND time value, however, when I use:

    datevalue(format(datefield, "mm-dd-yyyy hh:nn:ss"))

    I get: 1/4/2011

    With no time..(Looking up help on Datevalue, MS tells me that date isn't returned so this isn't unexpected)

    Is there a different function to use? I can't seem to find anything that will include both date and time and format accordingly (i.e. not as text)..

    Thanks for any assistance!

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    is this in a query?

    I am no expert on date/time stuff, but can you use right() and left() to split the string (via the space) in two parts and convert from there using one of more date functions?

  3. #3
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by ajetrumpet View Post
    is this in a query?

    I am no expert on date/time stuff, but can you use right() and left() to split the string (via the space) in two parts and convert from there using one of more date functions?
    Yes, its in a query,

    However, not sure if that would work without using 2 fields.

    As I see it, if I split up the data using right/left functions, I can concatenate the data back, but would be left with a text field again. I am trying to get a date AND time field not a text field..

    Access tables have both the date/time option. Is there perhaps a function that includes this as well?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by orcinus View Post
    Yes, its in a query,

    However, not sure if that would work without using 2 fields.

    As I see it, if I split up the data using right/left functions, I can concatenate the data back, but would be left with a text field again. I am trying to get a date AND time field not a text field..

    Access tables have both the date/time option. Is there perhaps a function that includes this as well?
    You know, I'm not really sure what the future is like for fields that are simply converted using built in resources. To be absolutely sure that the field is essentially date/time data type without question, I'd make a new table from the qry and see what data type comes out of it.

    as for the syntax, you want something similar to the formatting of the Now() function I assume. So why not try:
    Code:
    cdate(format(
    
    left(field, instr(field, " ") - 1) & " " & right(field, instrrev(field, " ") - 1)
    
    , "general date"))
    and see what happens? I'm sure this is over complex, but until someone offers something else, you might be stuck with me.

    <edit>
    I just noticed you're on 03, so you might have to substitute the following in for the instrrev() part:
    Code:
    ((len(field) - instr(field, " ")) - 1)

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Following your method:

    ?datevalue(format(#1/4/10 15:22#, "mm-dd-yyyy"))+timevalue(format(#1/4/10 15:22#, "hh:nn:ss"))
    01/04/2010 3:22:00 PM

    but even better might be:

    ?cdate("1/4/2011 15:42")
    01/04/2011 3:42:00 PM
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by ajetrumpet View Post
    You know, I'm not really sure what the future is like for fields that are simply converted using built in resources. To be absolutely sure that the field is essentially date/time data type without question, I'd make a new table from the qry and see what data type comes out of it.

    as for the syntax, you want something similar to the formatting of the Now() function I assume. So why not try:
    Code:
    cdate(format(
     
    left(field, instr(field, " ") - 1) & " " & right(field, instrrev(field, " ") - 1)
     
    , "general date"))
    and see what happens? I'm sure this is over complex, but until someone offers something else, you might be stuck with me.

    <edit>
    I just noticed you're on 03, so you might have to substitute the following in for the instrrev() part:
    Code:
    ((len(field) - instr(field, " ")) - 1)
    Didn't know about the cdate function. That's works straight up* without all the mucky muck!

    Perfect! Thanks..

    *cdate([textDateTimeField])

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by orcinus View Post
    Didn't know about the cdate function. That's works straight up* without all the mucky muck!

    Perfect! Thanks..

    *cdate([textDateTimeField])
    if you care to review, I would use Paul's, over mine, for the future. I simply gave you a ham-handed approach.

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

Similar Threads

  1. Date and Time tables
    By Pilotwings_64 in forum Database Design
    Replies: 5
    Last Post: 10-29-2010, 02:44 PM
  2. Date/Time Criteria
    By KWarzala in forum Queries
    Replies: 3
    Last Post: 06-04-2010, 07:08 PM
  3. Date + Time = Date Time
    By TundraMonkey in forum Access
    Replies: 3
    Last Post: 07-02-2009, 09:30 PM
  4. Date and time serch
    By gayano in forum Programming
    Replies: 1
    Last Post: 08-31-2007, 03:49 AM
  5. Access Date/Time....I need a "generic" date.
    By beastmaster in forum Access
    Replies: 2
    Last Post: 12-29-2005, 12:55 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