Week ending date problems

[FBxiii]

I am using the DateAdd function to try and calculate weekly figures.

we: DateAdd("d",-Weekday([Received])+6,[Received])

This is showing Fridays date and I want the week ending to be on a Sunday so
am using +8 which gives me the right date.

For some reason though, some figures are leaking through to the following
week and I cant figure out how to stop it.

e.g. we = 22/06/08 Received = 15/06/08

The 15/06/08 is a Sunday and should be counted in the Previous weeks 'we'
date. Can anyone tell me whats going wrong please?

Cheers,
Steve.

 

[KARL DEWEY]

Does the field Received include time with the date? If so then it would be
greater than Sunday midnight which is the cutoff.

Post your complete SQL.

 

[FBxiii]

No. The received field does not include the time.

Here is the SQL (showing Friday dates but its still not right):

SELECT DISTINCT DateAdd("d",-Weekday([Received])+6,[Received]) AS we,
tblAll.SSC, Count(tblAll.MPR) AS Rec_Count
FROM tblAll
GROUP BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC
ORDER BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC;


Cheers,
Steve.

Does the field Received include time with the date? If so then it would be
greater than Sunday midnight which is the cutoff.

Post your complete SQL.

--
KARL DEWEY
Build a little - Test a little

I am using the DateAdd function to try and calculate weekly figures.

we: DateAdd("d",-Weekday([Received])+6,[Received])

This is showing Fridays date and I want the week ending to be on a Sunday so
am using +8 which gives me the right date.

For some reason though, some figures are leaking through to the following
week and I cant figure out how to stop it.

e.g. we = 22/06/08 Received = 15/06/08

The 15/06/08 is a Sunday and should be counted in the Previous weeks 'we'
date. Can anyone tell me whats going wrong please?

Cheers,
Steve.

 

[John Spencer]

Not fully tested but try the following modification.

DateAdd("d",1-Weekday([Received],vbMonday),[Received])

WHOOPS - in a query you will need to replace vbMonday with 2. Queries have no
knowledge of VBA constants

DateAdd("d",1-Weekday([Received],2),[Received])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[FBxiii]

Hi. That looks like it gives me the week beginning date instead of the week
ending (we date is less than received date).

I have tried playing around with the numbers but still cant seem to get it
to work

Not fully tested but try the following modification.

DateAdd("d",1-Weekday([Received],vbMonday),[Received])

WHOOPS - in a query you will need to replace vbMonday with 2. Queries have no
knowledge of VBA constants

DateAdd("d",1-Weekday([Received],2),[Received])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am using the DateAdd function to try and calculate weekly figures.

we: DateAdd("d",-Weekday([Received])+6,[Received])

This is showing Fridays date and I want the week ending to be on a Sunday so
am using +8 which gives me the right date.

For some reason though, some figures are leaking through to the following
week and I cant figure out how to stop it.

e.g. we = 22/06/08 Received = 15/06/08

The 15/06/08 is a Sunday and should be counted in the Previous weeks 'we'
date. Can anyone tell me whats going wrong please?

Cheers,
Steve.

 

[KARL DEWEY]

Try this --
SELECT DateAdd("d",7-Weekday([Received],2),[Received]) AS We, tblAll.SSC,
Count(tblAll.MPR) AS Rec_Count
FROM tblAll
GROUP BY DateAdd("d",7-Weekday([Received],2),[Received]), tblAll.SSC;

--
KARL DEWEY
Build a little - Test a little

No. The received field does not include the time.

Here is the SQL (showing Friday dates but its still not right):

SELECT DISTINCT DateAdd("d",-Weekday([Received])+6,[Received]) AS we,
tblAll.SSC, Count(tblAll.MPR) AS Rec_Count
FROM tblAll
GROUP BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC
ORDER BY DateAdd("d",-Weekday([Received])+6,[Received]), tblAll.SSC;


Cheers,
Steve.

Does the field Received include time with the date? If so then it would be
greater than Sunday midnight which is the cutoff.

Post your complete SQL.

--
KARL DEWEY
Build a little - Test a little

I am using the DateAdd function to try and calculate weekly figures.

we: DateAdd("d",-Weekday([Received])+6,[Received])

This is showing Fridays date and I want the week ending to be on a Sunday so
am using +8 which gives me the right date.

For some reason though, some figures are leaking through to the following
week and I cant figure out how to stop it.

e.g. we = 22/06/08 Received = 15/06/08

The 15/06/08 is a Sunday and should be counted in the Previous weeks 'we'
date. Can anyone tell me whats going wrong please?

Cheers,
Steve.

 

[John Spencer]

So change the 1 to 7 to get the week ending date

DateAdd("d",7-Weekday([Received],2),[Received])
This returns 8/17/2008 for all dates between
Monday 8/11/2008 and Sunday 8/17/2008

If that is not what you need experiment with changing the number to 6 or 5 or
.... and then test it to see what results you do get.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi. That looks like it gives me the week beginning date instead of the week
ending (we date is less than received date).

I have tried playing around with the numbers but still cant seem to get it
to work

Not fully tested but try the following modification.

DateAdd("d",1-Weekday([Received],vbMonday),[Received])

WHOOPS - in a query you will need to replace vbMonday with 2. Queries have no
knowledge of VBA constants

DateAdd("d",1-Weekday([Received],2),[Received])


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am using the DateAdd function to try and calculate weekly figures.

we: DateAdd("d",-Weekday([Received])+6,[Received])

This is showing Fridays date and I want the week ending to be on a Sunday so
am using +8 which gives me the right date.

For some reason though, some figures are leaking through to the following
week and I cant figure out how to stop it.

e.g. we = 22/06/08 Received = 15/06/08

The 15/06/08 is a Sunday and should be counted in the Previous weeks 'we'
date. Can anyone tell me whats going wrong please?

Cheers,
Steve.