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!
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
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
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]
Try: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: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
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