Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Month format (MS Access 2000)

    I have a table that stores the date in the format - yyyymmdd. I have extracted the "mm" portion of the data with a query - Month: IIf([Birthdate]<>" ",(Mid$([Birthdate],5,2))). I would now like to convert the number of the month (01) to the text equivalent (Jan). Is there a way I can do this within the initial statement or do I modify the field after I have created the field, and if so what is the syntax?
    Thanks
    shihalud

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Month format (MS Access 2000)

    Try

    Month: IIf([Birthdate]<>" ",MonthName(Mid$([Birthdate],5,2), True)).

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Month format (MS Access 2000)

    Thanks Hans, I could not for the life of me find the word for this query, works great. What if I only want the abbreviation of the month, ie "Jan" for "January". Can I do this in the same statement or just use another statement to extract the left 3 letters?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Month format (MS Access 2000)

    The second argument to the MonthName function specifies whether the name should be abbreviated. If it is True, as in the example in my previous reply, you'll get Jan, Feb etc. If it is False or omitted, you'll get January, February etc.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Month format (MS Access 2000)

    Thanks again works perfectly

    shihalud

Posting Permissions

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