Results 1 to 7 of 7
  1. 08-14-2020, 06:50 AM #1
    BrittKnee
    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!
    Reply With Quote Reply With Quote
  2. 08-14-2020, 07:19 AM #2
    Bob Fitz's Avatar
    Bob Fitz
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,614
    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
    Reply With Quote Reply With Quote
  3. 08-14-2020, 07:57 AM #3
    Bob Fitz's Avatar
    Bob Fitz
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,614
    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
    Reply With Quote Reply With Quote
  4. 08-14-2020, 08:34 AM #4
    BrittKnee
    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]
    Reply With Quote Reply With Quote
  5. 08-14-2020, 08:45 AM #5
    Bob Fitz's Avatar
    Bob Fitz
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,614
    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
    Reply With Quote Reply With Quote
  6. 08-14-2020, 08:55 AM #6
    BrittKnee
    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!
    Reply With Quote Reply With Quote
  7. 08-14-2020, 09:01 AM #7
    Bob Fitz's Avatar
    Bob Fitz
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,614
    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
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. How to get the current week -1 week and CYTD -1 year (use a CYweek in an number format).
    By Emile in forum Access
    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. Dates including date, day of the week, week number and week ending
    By dwetexas in forum Code Repository
    Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  4. Sorting days of week in query and report access 2010
    By anlimah in forum Reports
    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
  •  

Forum Rules

Other Forums: Microsoft Office Forums