Week Number

[Guest]

Hello ;

I have two questions related to Week Number in access .
There is a field in my table that uses the current date and converts
that into week number by "ww" format .

(i) When i transfer the data into an excel , this field is shown as "ww" not
with the actual values .
(ii) I'd like to query the data by week number but that is also not possible
and i guess thats because this field holds the date information behind
therefore when grouped there can be many record
for the same week .

Can you pls help me to sort out above problems .
Thanks in advance;
Steve

 

[John Vinson]

Hello ;

I have two questions related to Week Number in access .
There is a field in my table that uses the current date and converts
that into week number by "ww" format .

How do you do the "conversion"? Thiw week field SHOULD NOT EXIST in
your table, since it can be derived on demand from the date field!

(i) When i transfer the data into an excel , this field is shown as "ww" not
with the actual values .

Again, how are you doing the export?

(ii) I'd like to query the data by week number but that is also not possible
and i guess thats because this field holds the date information behind
therefore when grouped there can be many record
for the same week .

Create a Query based on your table. If you have used the Lookup Wizard
or the format property of the field in the Table to display the week
number, don't: it just conceals the actual (date/time) contents of the
field. Instead, put in a calculated field by typing

WeekNumber: DatePart("ww", [datefield])

in a vacant Field cell. This field will now contain an integer from 1
to 54. You can export from this Query (rather than from the table)
into Excel.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi John ;

Thank you , that was very helpfull .

Steve

Hello ;

I have two questions related to Week Number in access .
There is a field in my table that uses the current date and converts
that into week number by "ww" format .

How do you do the "conversion"? Thiw week field SHOULD NOT EXIST in
your table, since it can be derived on demand from the date field!

(i) When i transfer the data into an excel , this field is shown as "ww" not
with the actual values .

Again, how are you doing the export?

(ii) I'd like to query the data by week number but that is also not possible
and i guess thats because this field holds the date information behind
therefore when grouped there can be many record
for the same week .

Create a Query based on your table. If you have used the Lookup Wizard
or the format property of the field in the Table to display the week
number, don't: it just conceals the actual (date/time) contents of the
field. Instead, put in a calculated field by typing

WeekNumber: DatePart("ww", [datefield])

in a vacant Field cell. This field will now contain an integer from 1
to 54. You can export from this Query (rather than from the table)
into Excel.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi John ;

Thank you, that was very helpful.

Steve

Hello ;

I have two questions related to Week Number in access .
There is a field in my table that uses the current date and converts
that into week number by "ww" format .

How do you do the "conversion"? Thiw week field SHOULD NOT EXIST in
your table, since it can be derived on demand from the date field!

(i) When i transfer the data into an excel , this field is shown as "ww" not
with the actual values .

Again, how are you doing the export?

(ii) I'd like to query the data by week number but that is also not possible
and i guess thats because this field holds the date information behind
therefore when grouped there can be many record
for the same week .

Create a Query based on your table. If you have used the Lookup Wizard
or the format property of the field in the Table to display the week
number, don't: it just conceals the actual (date/time) contents of the
field. Instead, put in a calculated field by typing

WeekNumber: DatePart("ww", [datefield])

in a vacant Field cell. This field will now contain an integer from 1
to 54. You can export from this Query (rather than from the table)
into Excel.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps