Returning a two digit week number using format or datepart

[Scotbot]

Hi,

I have a query which extracts the year and the week number from a date and appends another numeric field on the end so that for example

01/12/2010 1234

becomes Y10-W50-1234 (I may not have got the week number correct but you get the idea)


WeekBranch: "Y" & Right(DatePart("yyyy",[ActualStart],1,2),2) & "-W" & DatePart("ww",[ActualStart],1,2) & "-" & [TargetStoreCode]

The problem is that if the week number is between 1 and 9 it returns as a single digit and I get for example Y10-W5-1234.

I can't see any way of forcing either the format or the datepart functions to return a two digit week number - can anyone suggest a way to do this and if the functions can't do it some method of manipulating the result to check for single digit week numbers and append a 0 in front.

It occured to me that I could write an if statement and if the week number was 1-9 it would run the above formula but append "W0" instead of "W" in front of the week number?

Thanks! Scott.

 

[Galaxiom]

Nest the DatePart function in the Format function.

Format(DatePart("w",[ActualStart],1,2), "00")

 

[Scotbot]

Thanks very much, I didn't realise we could use format in that way.

Cheers!

 

[Galaxiom]

Format is an awesome function. Note that "yy" gives the last two digits of the year when applied to a date.

It can insert literals too by preceding the character with the backslash. Try something like this. It probably isn't quite right but will give you the idea. (I didn't chck what the 1,2 arguments do in DatePart and omitted them.)

Format([ActualStart], "\Yyy\-\W") & Format(DatePart("w", [ActualStart]),"00") & "-" & [TargetStoreCode]