Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unusual Date Format (Access 97 SR2 / NT4 SP5)

    Hi Everyone

    Bit of an odd one here that i am not having much success with, however, i suspect the answer is simple to those that already know the answer.

    I import (via Excel) data into a database. The client likes to use a 4 digit reference to indicate the month in which certain things happen, e.g. 0110 (October 2001) and 0201 (January 2002) etc, i.e. first two digits indicate year, and second two indicate month.

    I currently have this field set up as a text field, but now need to use the data's real purpose, i.e. maybe compare that field with this month, or do certain things to all rows where that field is in the future.

    I've tried changing the field to a date field, and formatting it as yymm, but this won't accept 4 digit data without a forward slash in between. I also export data back regularly and need this data to look the same as it was when they sent it to me.

    Thus is it possible to leave the field as it is, but each time i need to query it use some function to convert it to the yymm date format?????

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unusual Date Format (Access 97 SR2 / NT4 SP5)

    To convert the string to a date you could use something like:

    <pre>DateSerial(Left(datefield,2),Right(datefield, 2),1)
    </pre>


    You could do that in a calculated field in a query.
    Legare Coleman

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

    Re: Unusual Date Format (Access 97 SR2 / NT4 SP5)

    I'd import to a temporary table, then do an append query to take the temp table and add to to your regular data. For your date fields, you can use the DateSerial function to create a new date (using the 1st of each month), something like:
    NewDate = DateSerial(Left(olddate,2), right(olddate,2),1)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unusual Date Format (Access 97 SR2 / NT4 SP5)

    Guys

    Thank you so much for your prompt replies. Exactly what i wanted.

    Peter

Posting Permissions

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