Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2006
    Posts
    6

    Unanswered: Extract date from text and then sort

    Hi,

    I'm having trouble extracting date information from a text field using the query design mode.

    The field is named dtmDateRegistered. It is text(50) in size. The data in the field looks like 9/20/2005 12:10:14 PM.

    All I want is to get the date without time information, then do an actual date sort. Right now when I sort the field ascending I don't get dates in chronolgical order. I'm seeing something like:

    rec1 9/20/2005
    rec2 9/29/2005
    rec3 9/3/2005
    rec4 9/31/2005

    How can I get this to sort in chronological order? I've spend all day trying to figure this out. MS Access help hasn't been much help.

    Thanks in advance.

    JDB

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If you want to sort date strings as date values, then you need to convert the date strings to date values.

    There are several Date conversion functions, including DATE(). Add a filed to your query using this function to convert the text field to a date and sort by that field.

    tc

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by tcace
    If you want to sort date strings as date values, then you need to convert the date strings to date values.

    There are several Date conversion functions, including DATE(). Add a filed to your query using this function to convert the text field to a date and sort by that field.

    tc
    I think tcace means CDATE() not DATE(). As tcace says, this will convert your string to a date (which will include the time part). How you want the date to appear is done in the formatting i.e. format the field in the form, report or query which will ensure the time part isn't shown.

    You can also use DateValue() which will just return a date without the time part.

    Either method will then allow you to sort.

    hth
    Chris

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    The best suggestion, is probably to store it as date/time from the start. Thats probably why there is such a datatype ...
    Roy-Vidar

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    yep - my bad. The Date() function returns the current system date.

    I agree with Roy - if your date field is supposed to be a date, set it to a date data type. That guarentees it sorts correctly. Then, in your query, use the Format() function for format the date to the way you want it to look in the string.

    tc

  6. #6
    Join Date
    Jun 2006
    Posts
    6

    Date in text field

    Thanks for the help so far.

    Unfortunately, I don't have any control over the database and how data is stored...

    I'm not quite sure how to go about adding a field to the query builder pane. Would you just add a statment like FORMAT(CDATE(dtmDateRegistered),"MM/DD/YYYY") to a column in the pane? The click on Sort Ascending?

    I haven't done much with MS Access beyond using the Query Wizard.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I would Use the Split() command

    So build a function

    Function Get_My_data(data as string,returnthis as long)
    Dim Mydata() as string
    Mydata= Split(data," ",-1,vbTextCompare)
    Get_My_data = mydata(returnthis)
    end function

    so

    bla = Get_My_data("rec1 9/20/2005",1)

    bla would have the value of '9/20/2005'

    to use it in a query copy the fnction in a module

    in the query just Bla :Get_My_data([feildname],1)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Jun 2006
    Posts
    6

    Helpful

    myle,

    Thanks, I built the function and in the query put the bla statement and ran it. It worked sort of.. I got the time back without AM or PM on it. Didn't get the date.

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    the reason for no pm am was the split function slpited by the spaces

    if the value was "rec1 9/20/2005 08:30 pm"

    this split function

    Mydata(0) ="rec1"
    Mydata(1) ="9/20/2005"
    Mydata(2) ="8:30"
    Mydata(3) ="pm"

    you could join the as Bla :Get_My_data([feildname],1) & " " & Get_My_data([feildname],2)


    make cents
    Last edited by myle; 06-05-06 at 04:33.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Jun 2006
    Posts
    6

    Date in text field

    myle,

    Great, I got the extract part working. So now all I have in bla is date information. I think it is still in text format as it won't sort like a date. I'm assuming as Howey said, that CDATE() would be the way to go on this. Would the code look something like this CDATE(Get_My_data([feildname],1), "MM/DD/YYYY")?

    Thanks,

    JDB

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Yeap

    I think off the top of head it just cdate(Get_My_data([feildname],1))
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  12. #12
    Join Date
    Jun 2006
    Posts
    6
    Thanks, that worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •