Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DatePart('m',[dtDateChanged]) (Access 2003)

    Database is in Access 2000 file format, if that makes a difference.

    dtDateChanged is a date field (short date format). The field Expr1: DatePart("m",[dtDateChanged]) is in a query and returns the value 1/11/1900, when dtDateChanged = 12/16/2004. Using DatePart with the d and yyyy arguments works properly, returning day and year respectively. It's just the m argument that is giving me fits. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I was under the impression that the m argument is supposed to return the month. As far as I can see, it should have returned the value 12.

    What am I doing wrong? TIA.
    Carol W.

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

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    You should format the result as a number, not as a date.

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

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    Good question! The expression looks right so it must be the data in [dtDateChanged]. Try it on another date field.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    Thanks for the quick reply.

    Here is a screenshot of the query. How do I format Expr1 as a number? The other two fields, Expr2 and Expr3 are not formatted and they yield correct results.
    Carol W.

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

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    Click in the Expr1 field and activate the Properties window. Make sure that the Format property is either empty or set to a number format, not to a date format.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    The format drop-down was empty but I set it to "general number" just to be sure. That did the trick!

    Learned something new today. Thanks.
    Carol W.

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

    Re: DatePart('m',[dtDateChanged]) (Access 2003)

    Sometimes Access makes a wrong assumption about what type of value a calculation should return. Apparently, this was one of those situations.

Posting Permissions

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