Same week last year, MTD

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

"Klatuu" wrote:

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

"Klatuu" wrote:

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

"Klatuu" wrote:

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve

My user is telling me that he wants to see a sales analysis of Week 5. I
then need to translate that into dates for report headings and for selecting
data (which is stamped with the date, not the week number, and I can't change
that). So, if he tells me week 5, I need to know that week 5 translates to
1/29/07 - 2/4/07.

"Klatuu" wrote:

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

"Klatuu" wrote:

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

"Klatuu" wrote:

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

"Klatuu" wrote:

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve

Week 5 from what date? the dates you are showing in the example are the
beginning and ending weeks of week 1 of 2007. Week 5 of 2007 would be
3/5/2007
and from 2/4/07 would be 3/11/2007.

Pardon my thickness.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

My user is telling me that he wants to see a sales analysis of Week 5. I
then need to translate that into dates for report headings and for selecting
data (which is stamped with the date, not the week number, and I can't change
that). So, if he tells me week 5, I need to know that week 5 translates to
1/29/07 - 2/4/07.

"Klatuu" wrote:

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

"Klatuu" wrote:

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

"Klatuu" wrote:

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

"Klatuu" wrote:

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve

Oh ... gotcha. Week 5 of 2007, which is 1/29/07 - 2/4/07

"Klatuu" wrote:

Week 5 from what date? the dates you are showing in the example are the
beginning and ending weeks of week 1 of 2007. Week 5 of 2007 would be
3/5/2007
and from 2/4/07 would be 3/11/2007.

Pardon my thickness.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

My user is telling me that he wants to see a sales analysis of Week 5. I
then need to translate that into dates for report headings and for selecting
data (which is stamped with the date, not the week number, and I can't change
that). So, if he tells me week 5, I need to know that week 5 translates to
1/29/07 - 2/4/07.

"Klatuu" wrote:

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

"Klatuu" wrote:

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

"Klatuu" wrote:

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

"Klatuu" wrote:

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve

Oh, okay, I got it. So, if 2007 stated on 1/1/2007, then to get the 5th
week, you add 4 to it using the dateadd function to get 1/29/2007

Now, if the user wants to know the beginning day of the 5th week of the year
by putting a 5 in the text box. It would be:

dtmFirstWeek = DateSerial(Year(Date),1,1)
dtmFirstWeek = DateAdd("d", vbMonday - DatePart("w", dtmFirstWeek), _
dtmFirstWeek)
dtmSelectWeekStart= DateAdd("ww",Me.txtWeekNumber -1,dtmFirstWeek)
dtmSelectWeekEnd = DateAdd("d", vbFriday - DatePart("w", _
dtmSelectWeekStart), dtmSelectWeekStart)
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Oh ... gotcha. Week 5 of 2007, which is 1/29/07 - 2/4/07

"Klatuu" wrote:

Week 5 from what date? the dates you are showing in the example are the
beginning and ending weeks of week 1 of 2007. Week 5 of 2007 would be
3/5/2007
and from 2/4/07 would be 3/11/2007.

Pardon my thickness.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

My user is telling me that he wants to see a sales analysis of Week 5. I
then need to translate that into dates for report headings and for selecting
data (which is stamped with the date, not the week number, and I can't change
that). So, if he tells me week 5, I need to know that week 5 translates to
1/29/07 - 2/4/07.

"Klatuu" wrote:

sorry, Steve, I am still a little confused. I don't know what you are
looking for. If I add 5 weeks to 1/29/2007, I get 3/5/2007.
Can you explain what the objective is? Is it that you are trying to
detemine the beginning date of the next period based on the begining date of
the current period?

--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

HA!

A user will key into a week field the number 5 (for example)
I need to calculate that the beginning of that week (if Monday is my week
start) is 1/29/07 and the end of that week is 2/4/07.

"Klatuu" wrote:

How can I be an expert on dates when I seldom get one?
Can you give me an example of what you want?
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Thanks Dave. This is very helpful. I'll try this.

Since you seem to be an expert on dates, can you give me a formula to show
the beginning date and end date given only the week number?

"Klatuu" wrote:

This is one of the reasons I hate Accountants!

I am not exactly sure this will give you what you are needing, but my first
thought is to use the expression to calculate the first (calendar) day of the
month, then run that date through the expression that gives you the beginning
day (Monday) of the week. For example, if you calculate the first day of
February to be 2/1/2007 and look for that date's Monday, it will be
1/29/2007. For March (3/1/2007) it would return 2/26/2007. Hopefully that
will do it. If not, post back and let's see what we can do.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

The weekly formula gives me just what I'm looking for. Thank you.

The month beginning formula, however, just gives me the first of the month.
Our weeks begin on Monday, so, for example, the first day of February is
1/29/07. February begins with the first day of the 5th week. Does this make
sense?

Perhaps there's a function that recognizes the 4-4-5 pattern (weeks/month)
and can determine the first week number of a month? Or maybe I need to
figure this up in a formula??

"Klatuu" wrote:

Here is an expression that will return the starting date of the "same week
last year" based on the current date.
dateadd("ww", -52,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For example, For today's date (2/7/2007), it will return 2/6/2006 because
this week's monday is 2/5/2007

This part:
DateAdd("d", vbMonday - DatePart("w", Date), Date)

Returns the Monday for the current week.

If you want to know the week ending date:
dateadd("d",6,WeekStartDate)
That is, the result or the expression above that returns the week start
date. or:
dateadd("d",6,DateAdd("d", vbMonday - DatePart("w", Date), Date))

For the month to date last year:
dateserial(Year(date)-1,Month(date),1) will return the first day of the
current month of last year.
--
Dave Hargis, Microsoft Access MVP


"Steve Happ" wrote:

Using Access 2003

I have searched for quite a while through the discussions but can't seem to
find answers to what I need. If I've missed it, feel free to direct me to a
prior thread.

I need to create a query for a sales analysis report showing columns for:
This week
Same week last year
MTD (month to date) this year
Same period last year

The user enters the start and end dates for the desired week this year. (our
week runs from Monday to Sunday). The query for selecting the sales records
for this week this year is easy, selecting the data dates between the start
and end dates.

First question: How do I determine the start and end dates for the same
week last year?
Second question: How do I determine the start and end dates for the current
month to date and the same for same period last year?

Thanks!
Steve