Results 1 to 7 of 7
  1. #1
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17

    Sorting Week Number

    Hi! I currently have a query that returns the week number for a given date. I need week numbers 1-9 to include a 0 (01, 02, etc.) so that the weeks sort in the correct order. I currently use Datepart("ww",Assigned_Date). Thanks!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by BrittKnee View Post
    Hi! I currently have a query that returns the week number for a given date. I need week numbers 1-9 to include a 0 (01, 02, etc.) so that the weeks sort in the correct order. I currently use Datepart("ww",Assigned_Date). Thanks!
    Why do you need the preceding zero.
    Datepart("ww",Assigned_Date) will sort correctly.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by BrittKnee View Post
    Hi! I currently have a query that returns the week number for a given date. I need week numbers 1-9 to include a 0 (01, 02, etc.) so that the weeks sort in the correct order. I currently use Datepart("ww",Assigned_Date). Thanks!
    Try:
    Code:
    WkNum: IIf(CInt(DatePart("ww",[Assigned_Date]))<10,"0" & CInt(DatePart("ww",[Assigned_Date])),CInt(DatePart("ww",[Assigned_Date])))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17
    This works except for when Assigned_Date is blank. In this case it returned #error. I tried:

    Code:
    IIf(CInt(DatePart,("ww",Assinged_Date=""))),"",IIf(CInt(DatePart("ww",[Assigned_Date]))<10,"0" & CInt(DatePart("ww",[Assigned_Date])),CInt(DatePart("ww",[Assigned_Date]))))


    Thanks!



    [/code]

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by BrittKnee View Post
    This works except for when Assigned_Date is blank. In this case it returned #error. I tried:

    Code:
    IIf(CInt(DatePart,("ww",Assinged_Date=""))),"",IIf(CInt(DatePart("ww",[Assigned_Date]))<10,"0" & CInt(DatePart("ww",[Assigned_Date])),CInt(DatePart("ww",[Assigned_Date]))))

    Thanks!
    Try:

    Code:
    WkNum: IIf(CInt(DatePart("ww",Nz([Assigned_Date],0)))<10,"0" & CInt(DatePart("ww",Nz([Assigned_Date],0))),CInt(DatePart("ww",Nz([Assigned_Date],0))))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    BrittKnee is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    17
    This returned 52 for all records that do not have an assigned date. I appreciate your help!

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by BrittKnee View Post
    This returned 52 for all records that do not have an assigned date. I appreciate your help!
    Hopefully this will return 60

    WkNum: IIf(CInt(DatePart("ww",Nz([Assigned_Date],60)))<10,"0" & CInt(DatePart("ww",Nz([Assigned_Date],60))),CInt(DatePart("ww",Nz([Assigned_Date],60))))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-14-2016, 06:30 PM
  2. week number sorting
    By sdel_nevo in forum Queries
    Replies: 2
    Last Post: 01-13-2016, 09:22 AM
  3. Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  4. Replies: 4
    Last Post: 07-15-2014, 01:58 PM
  5. Sorting By week
    By BringTheR41n in forum Reports
    Replies: 12
    Last Post: 07-14-2014, 02:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums