=format([dte], "ww yyyy", 7, 1)

[umair434]

=format([dte], "ww yyyy", 7, 1) query displays the weeks and the year - but they are not displayed in an ascending order. When I try to sort them, there is no option of "smallest to largest", rather "Sort A to Z" appears :S.

data appears like this:
Week
1
10
11
12
..
19
2
21

thanks!

 

[boblarson]

A To Z IS from smallest to largest in this case.

 

[umair434]

Hi bob,

thanks for helping me out here as well. I did that but the sorting doesn't change.

I still see the entries as follows:

1 2011
102010
112010..


how can I fix that?

Also, I want to make a pivot chart comparing 2011's week numbers to 2010 week numbers.

thanks again bob!

 

[boblarson]

format returns a string so it will be sorting as text values. If you want it with numbers then you will be using something like:

=CInt(format([dte], "ww yyyy", 7, 1))

 

[umair434]

I thought so that too, but using =Cint(format([dte], "ww yyyy", 7, 1)) gives an error

"data type mismatch in criteria expression"

 

[boblarson]

That would be because you have nulls in the dte field. You would need to either exclude nulls or use the NZ function around dte to display a certain date if there was a null.

 

[gemma-the-husky]

surely sort the query, by the date itself

the formatting is just for presentation, isn't it?

I am not sure Bob is right, though. once a date is formatted as ww yyyy, i don't see how you can convert it back to a long with cint, or more probably clng

 

[umair434]

Gemma-the-huskey: I just wanted to compare the 2010 data vs 2011 data in a pivot chart. x axes being the weeks from 1-52, so I assumed the table should be in order :S

I grouped by date and by weeks (making date unchecked) - the result is displayed like

week 1 2010
week 1 2010
week 2 2010
week 2 2010...

meaning it's not summing per week for a year.

I don't care about the presentation of the data as long as I can get my chart to work - how would I approach that?
bob: I removed the nulls and it still gives the same result.

 

[boblarson]

I am not sure Bob is right, though. once a date is formatted as ww yyyy, i don't see how you can convert it back to a long with cint, or more probably clng

You could if there wasn't a space (I just realized that is what is keeping it from happening). Without the space it should work just fine (if you use a long - yes, 522011 would be quite a bit over the 32,565 limit for an int) but, it should be yyyyww if it is going to be sorted by the year and weeks within that year.

 

[vbaInet]

Sorting on [dte] should be sufficient. No need to sort on your format().

 

[umair434]

sorting on [dte] doesn't sort on "weeks of a specific year" though, right?

I have data from 1st Jan 2010 to current. I want to compare any current week with the same week from last year. For example Week 39 2011 vs Week 39 2010!

I assumed Pivot chart would be ideal. How should I approach this?

thanks!

 

[vbaInet]

You are sorting by [dte] not by Format([dte], "ww yyyy"). Just untick the Show checkbox under [dte] and apply a sort under it.

 

[umair434]

okay! i think I am not following you properly. Sorry about that.

I am attaching the screeshot of my SQL design view window - this was before my made any changes whatsoever.

result for this query is as follows:


Week
1
10
11
12

thanks!

 

[vbaInet]

As long as [dte] is a real Date/Time field:

1. Add [dte] to your query
2. Untick the Show checkbox
3. Apply Ascending sort under the Sort row of [dte]

 

[umair434]

got it working! thanks a bunch everyone

 

[vbaInet]

Glad to hear!

 

[umair434]

Hi guys!

I got the Pivot chart working, but I had to include =datepart("ww", [dte], 7, 1) in another column for the 2011 data to be on top of 2010 data. Here is my SQL:

SELECT tblActivities.dte, DatePart("ww",[dte],7,1) AS Week, Sum(tblActivities.[High Cube Available]) AS [SumOfHigh Cube Available], Sum(tblActivities.[High Cube Actioned]) AS [SumOfHigh Cube Actioned]
FROM tblActivities
GROUP BY tblActivities.dte, DatePart("ww",[dte],7,1)
HAVING (((DatePart("ww",[dte],7,1)) Between [forms]![filter]![weekfrom] And [forms]![filter]![weekto]));


Now when I enter "1" as week from and "2" as week to. I get weeks from 1 to 19!

If I enter "1" and "10", I get weeks 1 and 10 only!

how should I get rid of this?

thanks!

 

[vbaInet]

What about:

SELECT tblActivities.dte, DatePart("ww",[dte],7,1) AS Week, Sum(tblActivities.[High Cube Available]) AS [SumOfHigh Cube Available], Sum(tblActivities.[High Cube Actioned]) AS [SumOfHigh Cube Actioned]
FROM tblActivities
GROUP BY tblActivities.dte, DatePart("ww",[dte],7,1)
HAVING (((DatePart("ww",[dte])) Between [forms]![filter]![weekfrom] And [forms]![filter]![weekto]));

 

[umair434]

What about:

SELECT tblActivities.dte, DatePart("ww",[dte],7,1) AS Week, Sum(tblActivities.[High Cube Available]) AS [SumOfHigh Cube Available], Sum(tblActivities.[High Cube Actioned]) AS [SumOfHigh Cube Actioned]
FROM tblActivities
GROUP BY tblActivities.dte, DatePart("ww",[dte],7,1)
HAVING (((DatePart("ww",[dte])) Between [forms]![filter]![weekfrom] And [forms]![filter]![weekto]));

same thing! I think it's taking weeks as:
1
10
11
12
13
14
15
16
17
18
19
2
21

that's why when I enter "1" and "2", I get from weeks 1 to 19!

 

[vbaInet]

Try >= and <=

Also, try converting to Integer on all three sides.