Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format question (access 2003)

    I have an date id values of 20081016A or 20081016B (10/16/2008) in a query. I would like combine the totals for these 2 date id values by doing a group by query. I need the date value ids to be equal (20081016). I know I can strip of the A or the B using a right function but the problem with this is sometimes the length of these date value ids can vary. I have same date ids stored like 2008116A or 2008116B (which is for 1/1/2008). Is there a way for me to add something to my query which will remove the last character regardless of the string length? Thanks...

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: format question (access 2003)

    To get the date only part:

    Mid(DateField, 1, Len(DateField)-1)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format question (access 2003)

    Thanks Pat..

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: format question (access 2003)

    or :

    Left(DateField, Len(DateField)-1)

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: format question (access 2003)

    >>...I have same date ids stored like 2008116A or 2008116B (which is for 1/1/2008). "

    I'm still trying to figure out how you get 1/1/2008 out of 2008116B!

    I assume you meant to say 1/16/2008; but really, it could also be 11/6/2008!

    You will need to pass the ID to a function that examines the ID and then makes some decisions as to how to handle it based on how long it is and the contents. But even then, I don't know how you can decisively determine what date is represented by 2008116B.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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