convert 'yyyy ww' to an actual date

[neenmarie]

I use the following format to get the totals of a field by week.
Format$([TransactionSub1b].[Date],'yyyy ww') This allows me to sum all
records within a week...and shows the date as '2004 45'... etc. After
getting the totals, I'd like to be able to show the week as an actual date in
a report (such as the first date of week 45). Is there some way to do this?
Or does anyone know of a calendar that I could put into a table to connect to
this query that would know that 2004 45 = 06/06/04? (or whatever 2004 45
really does =)

 

[Duane Hookom]

Just add the year([TransactionSub1b]) and DatePart("ww", [TransactionSub1b])
into your query. You can then use these values to calculate a date value.

 

[Limo]

Look under "Example Expressions" in the help search.

There you will find:
=Format(Now(), "ww") Uses the Format function to display the number of the
week of the year the current date represents, where ww is 1 through 53.

 

[neenmarie]

I've tried to work through both of these responses, but still cannot
understand how to do this. I've already changed the dates to yyyy ww in
order to group weekly transactions in a report. I'd like to convert the yyyy
ww back to the first day of the week such as change "2004 56" to "2004 12 19"

Look under "Example Expressions" in the help search.

There you will find:
=Format(Now(), "ww") Uses the Format function to display the number of the
week of the year the current date represents, where ww is 1 through 53.

I use the following format to get the totals of a field by week.
Format$([TransactionSub1b].[Date],'yyyy ww') This allows me to sum all
records within a week...and shows the date as '2004 45'... etc. After
getting the totals, I'd like to be able to show the week as an actual date in
a report (such as the first date of week 45). Is there some way to do this?
Or does anyone know of a calendar that I could put into a table to connect to
this query that would know that 2004 45 = 06/06/04? (or whatever 2004 45
really does =)

 

[Duane Hookom]

There is nothing preventing you from adding two more columns to your
report's record source.
TheWeekatePart("ww", [TransactionSub1b])
TheYear: Year([TransactionSub1b])
You could also add a column in your record source
StartOfWeek:
DateAdd("d",-Weekday([TransactionSub1b])+1,[TransactionSub1b])

--
Duane Hookom
MS Access MVP
--

I've tried to work through both of these responses, but still cannot
understand how to do this. I've already changed the dates to yyyy ww in
order to group weekly transactions in a report. I'd like to convert the
yyyy
ww back to the first day of the week such as change "2004 56" to "2004 12
19"

Look under "Example Expressions" in the help search.

There you will find:
=Format(Now(), "ww") Uses the Format function to display the number of
the
week of the year the current date represents, where ww is 1 through 53.

I use the following format to get the totals of a field by week.
Format$([TransactionSub1b].[Date],'yyyy ww') This allows me to sum all
records within a week...and shows the date as '2004 45'... etc. After
getting the totals, I'd like to be able to show the week as an actual
date in
a report (such as the first date of week 45). Is there some way to do
this?
Or does anyone know of a calendar that I could put into a table to
connect to
this query that would know that 2004 45 = 06/06/04? (or whatever 2004
45
really does =)

 

[neenmarie]

You are a genius. Thank you very much

There is nothing preventing you from adding two more columns to your
report's record source.
TheWeekatePart("ww", [TransactionSub1b])
TheYear: Year([TransactionSub1b])
You could also add a column in your record source
StartOfWeek:
DateAdd("d",-Weekday([TransactionSub1b])+1,[TransactionSub1b])

--
Duane Hookom
MS Access MVP
--

I've tried to work through both of these responses, but still cannot
understand how to do this. I've already changed the dates to yyyy ww in
order to group weekly transactions in a report. I'd like to convert the
yyyy
ww back to the first day of the week such as change "2004 56" to "2004 12
19"

Look under "Example Expressions" in the help search.

There you will find:
=Format(Now(), "ww") Uses the Format function to display the number of
the
week of the year the current date represents, where ww is 1 through 53.


:

I use the following format to get the totals of a field by week.
Format$([TransactionSub1b].[Date],'yyyy ww') This allows me to sum all
records within a week...and shows the date as '2004 45'... etc. After
getting the totals, I'd like to be able to show the week as an actual
date in
a report (such as the first date of week 45). Is there some way to do
this?
Or does anyone know of a calendar that I could put into a table to
connect to
this query that would know that 2004 45 = 06/06/04? (or whatever 2004
45
really does =)

 

[sandip]

Hi
I had same problem and found some easy way
In my main query I made expresssion field as
Start Date: Format([date]-Weekday([date],2)+1,"mm-dd-yyyy")
End Date: Format([date]-Weekday([date],2)+7,"mm-dd-yyyy")
and thsi I am using when ever I need start or End date of perticula
week
see if it works for you
Sandi


-
sandi