Month of year in code

[likeyeah]

I have used a function to develop week of the year from the date in a table, so the event is allocated to a
specific week, for example this week is 1212, next week 1213 for Year 2012 - Week 13.

I would like to round these up as it is going to form the x axis of a graph, and by month is the correct
granularity for that axis.

However, as the chart object in Access is limited, and if you extract the data in the format using format
([DateVariable], "mmm","yyyy") you can't sort the axis properly as it will do a text sort and be incorrect on the
timeline.

Has anyone developed a sort routine to give either a conversion of the week number to Month and year and sort
ascending, so the graph timeline is correct?

Thanks

 

[DawnTreader]

I have used a function to develop week of the year from the date in a table, so the event is allocated to a
specific week, for example this week is 1212, next week 1213 for Year 2012 - Week 13.

I would like to round these up as it is going to form the x axis of a graph, and by month is the correct
granularity for that axis.

However, as the chart object in Access is limited, and if you extract thedata in the format using format
([DateVariable], "mmm","yyyy") you can't sort the axis properly as it will do a text sort and be incorrect on the
timeline.

Has anyone developed a sort routine to give either a conversion of the week number to Month and year and sort
ascending, so the graph timeline is correct?

Thanks



--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

why not use year(yourDateHere) & format(yourDateHere,"mm")

or

format(yourDateHere, "yyyy-mm")

these wont return periods that will sort alphabetically and screw up the timeline. you can sort ascending or descending. the only thing i dont like about it is that you dont get a text month identifier, but other than that this works all over and keeps things in sequential order.

 

[DawnTreader]

I have used a function to develop week of the year from the date in a table, so the event is allocated to a
specific week, for example this week is 1212, next week 1213 for Year 2012 - Week 13.

I would like to round these up as it is going to form the x axis of a graph, and by month is the correct
granularity for that axis.

However, as the chart object in Access is limited, and if you extract the data in the format using format
([DateVariable], "mmm","yyyy") you can't sort the axis properly as it will do a text sort and be incorrect on the
timeline.

Has anyone developed a sort routine to give either a conversion of the week number to Month and year and sort
ascending, so the graph timeline is correct?

Thanks



--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

oh and you could do

format(yourDateHere,"yyyy-ww") and that should give the week number instead of month.