Week ending date problems

  • Thread starter Thread starter FBxiii
  • Start date Start date
F

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.
 
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.
 
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.


KARL DEWEY said:
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


FBxiii said:
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.
Click to expand...
Click to expand...
 
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
 
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 :(


John Spencer said:
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.
Click to expand...
Click to expand...
 
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


FBxiii said:
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.


KARL DEWEY said:
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


FBxiii said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 :(


John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top