Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    45

    Red face Unanswered: Calculate number of week in NUMBER Data type?

    I have a Query in which one of the fields is used to calculate number of week from a date.

    WeekNumber: Format([Date];"ww";2;3)

    When this Query is in PivotTableView the value in calculated field have "text Data type" . In filter respectively numbers of weeks are sorted 1,10,11,2,3.. like text!!!

    I want numbers of weeks to be in “number Data type” and in filter to be sorted like numbers.

    Probably I use the wrong function in calculation? Or is there a way to convert data type?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could try an explicit cast to a numeric type


    eg
    cint(Format([Date];"ww";2;3))
    not too sure if it will work, different SQL engines have different ways of returning information, som ereturn the data type, some, return string, soemtimes it depends on the front end

    failing that you could try to format the week number with a leading 0
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Posts
    45
    It works!!!
    Thanks!

Posting Permissions

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