Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    7

    Unanswered: Removing the text around a date...

    Is there any way to remove all of the text from a field in a query?

    I have a bunch of database tables linked in Access with ODBC. Some of the data that I need to pull are dates.

    If the field reads: "DEPT#.00040 MUST ARRIVE BY 08/09/04" I need to be able to pull it out in such a way that it will read "09/09/04" and leave the rest of the extraneous stuff out.

    Is there an easy way to do this? Not all of the data is in the same format, so I can't just truncate the field...

    Thanks!

    -=B-=

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll need to manually parse out the date. I would reverse the string, then pull out everything up to the first " ", then reverse your result.

    functions to look at would be InStr, Left and Reverse.

  3. #3
    Join Date
    Jun 2004
    Posts
    7
    What's going to get me, though, is something like "DEPT#:00040 NO DEL AFTR:04/10/04" where there is no space. I am exploring the possibility of using a substring to pull out only what is around the /'s... maybe 3 or 4 characters on each side, and then I can whittle it down by removing all of the A's in position 1, all of the B's in position 1, etc... including the :'s.

    -=B-=
    Last edited by The1Bill; 08-10-04 at 12:56.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is the date always DD/MM/YY ?
    is it always last in the string ?

    if yes: right$(whatever, 8) holds your "date"

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2004
    Posts
    7
    I wish it were that simple. I just found one that says "MUST DELIVER BEFORE 5/1" What makes this even a bit more harrowing is that there are 2 fields of ship description, either one of which can hold the date. I think that it would be best to take the last 8 of both fields and concatanate them. Then, I could either select the first 8 if "SHIP_INSTRUCTION_1" contains a /, or the second 8 if "SHIP_INSTRUCTION_2" contains a /. Think that would work? Then I would be able to trim out anything that isn't a number or a /.

    -=B-=

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If the dates aren't even date format, I don't see how you can explain to a computer how to do this.

    Maybe search for the first value that's not an integer or a "/" and split it that way? That still doesn't help if there isn't a year supplied
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Posts
    7
    That's my dilemma. This data has been put in by about 30 different people, and they all have a different way of doing this. The only saving grace is that I need to do this for the past few months, so it is all 2004. If I have a 5/1, I could always append it to 05/01/04, which makes it an actual date format. I foresee this taking a lot fo queries that will need to be run in succession, so I will just try to automate it, I guess, since I cannot change the original data.
    -=B-=

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    simple solution: condemn the person(s) who entered the crappy data to tidy it all up.

    with dates varying from "5/1" thru "5/1/04" thru "05/01/04" thru "05/06/2004" (...and probably worse) AND add in US vs ROW date format and you have NO chance of an auto-parse.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Alright, assuming you have ONLY two possible date formats, you can do this, but it's going to suck.

    First, reverse the string. Then you'll have to evaluate each character in the string until you get to the first character that's not either an integer or a "/". Then you'll have to use the position of that character as an argument to the left() function and pull o ut your "date" into a string variable. Then you have to reverse your string again, so it's in the original order. Then you'll have to count how many slashes are in the string. Then you'll have to decide whether to append "04" to the end.

    Then you can dump it into a valid dateformat field.

    Fun huh?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2004
    Posts
    7
    Well, I have all day to make this work, and I am the only person who needs to run this, so I can get away with doing this somewhat messy. You're right, though, that does sound like it sucks. Thanks for the replies...
    -=B-=

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    reversing the string is one way
    consider also the function instrrev()

    ...it still sucks.

    izy
    currently using SS 2008R2

Posting Permissions

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