Week Start Date

[Ed]

Hi All: any help would be great. I set up a calc field in query that returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change the
global format of the database or just this field to produce results for week
starting on Monday? Thanks, Ed

 

[Marshall Barton]

Hi All: any help would be great. I set up a calc field in query that returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change the
global format of the database or just this field to produce results for week
starting on Monday?

Check Help on the Format function, it has optional arguments
to specify how you want to handle that kind of thing.

 

[Ed]

Thanks but could not find how to set starting point of week.

Hi All: any help would be great. I set up a calc field in query that
returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change the
global format of the database or just this field to produce results for
week
starting on Monday?

Check Help on the Format function, it has optional arguments
to specify how you want to handle that kind of thing.

 

[Marshall Barton]

Thanks but could not find how to set starting point of week.

Are you sure you looked up the Format function in Help? It
clearly explains that the 3rd agrument specifies the
starting day of the week. For Monday, use:
Format([Date], "ww", 2)
--
Marsh
MVP [MS Access]

Hi All: any help would be great. I set up a calc field in query that
returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change the
global format of the database or just this field to produce results for
week
starting on Monday?

"Marshall Barton" wrote
Check Help on the Format function, it has optional arguments
to specify how you want to handle that kind of thing.

 

[Ed]

Thanks, Hate to have someone do my work but could not and still cannot find
any example of the date function showing second part of format. Maybe my
help is restricted? Thanks, Ed

Thanks but could not find how to set starting point of week.

Are you sure you looked up the Format function in Help? It
clearly explains that the 3rd agrument specifies the
starting day of the week. For Monday, use:
Format([Date], "ww", 2)
--
Marsh
MVP [MS Access]

Ed wrote:
Hi All: any help would be great. I set up a calc field in query that
returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change
the
global format of the database or just this field to produce results for
week
starting on Monday?

"Marshall Barton" wrote
Check Help on the Format function, it has optional arguments
to specify how you want to handle that kind of thing.

 

[Marshall Barton]

Not all your fault, Ed. Help's search facility is
distinctly deficient in the last several versions (A97 was
very good, and A2 was excellent), but it is slowly improving
since A2K.

To find all the formatting codes, start by looking up
Format Property
and follow the See Also items.
--
Marsh
MVP [MS Access]

Thanks, Hate to have someone do my work but could not and still cannot find
any example of the date function showing second part of format. Maybe my
help is restricted? Thanks, Ed

Thanks but could not find how to set starting point of week.

"Marshall Barton" wrote
Are you sure you looked up the Format function in Help? It
clearly explains that the 3rd agrument specifies the
starting day of the week. For Monday, use:
Format([Date], "ww", 2)

Ed wrote:
Hi All: any help would be great. I set up a calc field in query that
returns
the week number for a given date Week: (Format([Date],"ww")).
Problem is this is based on the week starting on Sunday. Can I change
the
global format of the database or just this field to produce results for
week
starting on Monday?


Check Help on the Format function, it has optional arguments
to specify how you want to handle that kind of thing.

 

[sandip]

Can you suggest how to reverse this data
what i mean is i have query which shows week number converted with hel
of datepart function to sum perticular field on week basis
now i want to run report which shows start date and end date of thi
week number How to do this
Sandi


-
sandi

 

[Dale Fye]

Sandip,

To get the start date of the week that we are in today, you could use the
following:

DateAdd("d", -1 * Datepart("w", date()), Date())

This subtracts the day of the week from todays date, to get the first day of
this week. To get the first day of the week for each record in your
database table you would use something like:

WeekStart: DateAdd("d", -1 * DatePart("w", [SalesDate]),
DateValue([SalesDate])

You can then add 6 to that value to get the date for the end of the week.

HTH
Dale

 

[Dale Fye]

Correction,

That should be: DateAdd("d", (-1 * (Datepart("w", date())) +1), Date())

The previous code actually gives you the last day of the previous week.

Sorry!

Sandip,

To get the start date of the week that we are in today, you could use the
following:

DateAdd("d", -1 * Datepart("w", date()), Date())

This subtracts the day of the week from todays date, to get the first day of
this week. To get the first day of the week for each record in your
database table you would use something like:

WeekStart: DateAdd("d", -1 * DatePart("w", [SalesDate]),
DateValue([SalesDate])

You can then add 6 to that value to get the date for the end of the week.

HTH
Dale

Can you suggest how to reverse this data
what i mean is i have query which shows week number converted with help
of datepart function to sum perticular field on week basis
now i want to run report which shows start date and end date of this
week number How to do this
Sandip

 

[sandip]

Thanks Dale
Problem is I have query which has weeks Numbers from 1 to 53
of year 2004 with some data and I need now Start date and End Date of
selected week No. For eg. If I select
week no 43 i need to know what is Start date and End date of that weeek
no. and I tried with your code but it gives Error Message
Sandip

 

[Dale Fye]

There is probably an easier way, but it does not come to mind right now. I
have a table and a query that I use frequently when working with dates.
These will come in handy now.

1. Create a new table (tbl_Numbers) in your database. The table should
contain a single integer field (int_Value), and should have ten entries (the
numbers 0-9).

2. Once you have the table created, create a new query (qry_NumbersTo999)
and paste the following into the SQL view of the query. This query will
result in a set with values from 0-999.

SELECT Hundreds.int_Value * 100 + Tens.int_Value * 10 + Ones.int_Value as
intNumber
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

3. Create another query to get the first Day a particular week. The way
this query is written, it asks for a year and a week value.

PARAMETERS [What Week] Short;
SELECT Min(DateAdd("d",[intNumber],DateSerial(Year(Date()),1,1))) AS
FirstDayOfWeek
FROM qry_NumbersTo999
WHERE
DatePart("ww",DateAdd("d",[intNumber],DateSerial(Year(Date()),1,1)))=[What
Week];

This is probably a little confusing, so let me try to explain. The
DateSerial expression returns January 1st of the current year. When I use
the DateAdd() function to add all of the numbers in the qry_NumbersTo999 to
it, I get a recordset with 1000 days. The WHERE clause guarantees that I
limit the results set to only those records that are in the specified week.
The MIN() clause guarantees that I only get the earliest date that meets
this criteria.

HTH
Dale

 

[sandip]

*Thanks Dale
Problem is I have query which has weeks Numbers from 1 to 53
of year 2004 with some data and I need now Start date and End Date of
selected week No. For eg. If I select
week no 43 i need to know what is Start date and End date of that
weeek no. and I tried with your code but it gives Error Message
Sandip *

Hi
Still waiting for some solution on above
Please if any one can help??
Thanks in advance
Sandip