Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    I wish to extract the month from a date field, i.e. Month([period]), and then display it as it's text value i.e 7 = July. However, I don't seem to be able to format it as a text only setting. All I'm getting is the number each time. I don't particularly want to do a long line of if then's and I also need to manipulate the extract as say Month([period])-1 etc. and display several of these alongside as well.

    Is there something I'm missing on the formatting side or does anyone have a clever work around?

    Cheers,

    NIven <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    Just create a text box bound to the Period field, and type mmmm in its Format property.

    m = month number without leading zero: 1, 2, ..., 12.
    mm = month number with leading zero: 01, 02, ..., 12.
    mmm = abbreviation of month name: Jan, Feb, ..., Dec.
    mmmm = full month name: January, February, ..., December.

    Do not use =Month([Period]) here.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    Cheers for that, just what I was looking for!

    Niven <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    Further to the original post (please advise if I should re-submit this as a new post), I also have to summarise the text part of the date field, but am having problems when it comes to say crosstabbing by Jan, Feb, Mar etc. Also if I append the extracts to another table, the data is still held as the original date. i.e. it may say Feb, but when you click on the field it shows as 16/02/2004.

    Is there a way of just getting the month text value out, without it keeping the original date value? I know I could just take the month number and perhaps link it to a table of months, but it would be nice to be able to take the 3 letter text month from the off.

    Cheers,

    Niven

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

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    You can create a column

    Month3: Format([period], "mmm")

    but you have to be aware that this is a text column, so if you sort on it, the order will be Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    Good man!

    Just what I was looking for, spent ages puzzling over that, but knew there had to be a simple method,

    Cheers,

    Niven

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    As a less good/elegant alternative you could use:-

    Choose([Period],"January","February","March","April","May","June" ,"July","August","September","October","November", "December")

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    As soon as you post you notice the cockup! I meant :-


    Choose(Month([Period]),"January","February","March","April","May","June ","July","August","September","October","November" ,"December")

  9. #9
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating date fields in Reports (2000 (9.0.4402 SR-1))

    Very useful, thanks for that!

    Niven

Posting Permissions

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