Criteria to delete last 3 days of data

[kimb via AccessMonster.com]

I have searched Access Monster and I have been unable to answer my question.

I am trying to come up with a formula to put in my query that would remove
records based on my field "Date Scanned". I need to remove the last 3 days,
including today. One of my problems is the weekend. If it is Monday then I
would need it to delete, Thursday, Friday, Saturday, Sunday and Monday. If it
is Tuesday, I would need to delete, Friday, Saturday, Sunday, Monday and
Tuesday, and then on Wednesday, I would delete, Monday, Tuesday, Wednesday,
and so on.

I found this in Access Monster: IIf(Weekday(Date())=2,DateDiff("d",-3,Date()),
DateDiff("d",-1,Date())). This was used to do almost the opposite. It was to
keep yesterday and today's data and delete the rest. I have tried to
manipulate this for my purposes, but I have been unable to figure it out.

Any help would be greatly appreciated!
Kim

My Query (Table contains same)
Tracking Number Date Scanned Time Day Activity
Location
1Z2222222222222222 2/2/2010 5:39 AM Tue DESTINATION SCAN
EDMONTON, AB, CA

 

[Jerry Whittle]

Check out the DateAdd function as it has a weekday setting where it skips
weekends (but not weekday holidays). Something like below

DateAdd("w",-3,Date())

Also a comment about your table. You have three fields where you only need
one. The Date Scanned could include both the date and time in one field.

As for the name of the day, you could use the format function to see it as
needed.

Format(Date(),"ddd") = Tue
Format(Date(),"dddd") = Tuesday

 

[kimb via AccessMonster.com]

I understand where your coming from by saying I don't need as many fields as
I do. However, the kind of reports that I run requires this data to be
seperated. Everything must be pivotable. This information is used for
analysis so everything is broken down as far has it can be.

I don't actually want to "skip" weekends. What I need is to delete 3 weekdays
worth of data, except if a weekend falls in between. I will have data for the
weekend, and since it falls in between my 3 weekday window then that data
needs to be deleted as well. (I know this is convulted, but it is necessary)

For example: If today is Wednesday, then I need to delete Monday Tuesday &
Wednesday. But if it is Tuesday, then I need to delete, Friday, Saturday,
Sunday, Monday & Tuesday.

Check out the DateAdd function as it has a weekday setting where it skips
weekends (but not weekday holidays). Something like below

DateAdd("w",-3,Date())

Also a comment about your table. You have three fields where you only need
one. The Date Scanned could include both the date and time in one field.

As for the name of the day, you could use the format function to see it as
needed.

Format(Date(),"ddd") = Tue
Format(Date(),"dddd") = Tuesday

I have searched Access Monster and I have been unable to answer my question.

[quoted text clipped - 19 lines]

1Z2222222222222222 2/2/2010 5:39 AM Tue DESTINATION SCAN
EDMONTON, AB, CA

 

[John Spencer]

Jerry, I'm sorry but using "w" in the DateAdd function gives the same result
as using "d". At least, I've never gotten any other behavior from it.

Start Date
DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-3),Date)

So the criteria under the Date Scanned field would be
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date

You might want to change the first number in the choose statement to - 5
(Sunday) if you wanted W, Th, F deleted if you run this on Sunday. And you
would need to change the last -2 to -3 if you wanted to delete W, Th, F if you
run this on Saturday.


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

 

[kimb via AccessMonster.com]

Thanks John! But i'm having some difficulties. I copied:
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date
and pasted it in my criteria. When I run the query it's telling me there is
a "Data Type Mismatch in Criteria Expression"

Do you know how I can fix this?

Kim

Jerry, I'm sorry but using "w" in the DateAdd function gives the same result
as using "d". At least, I've never gotten any other behavior from it.

Start Date
DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-3),Date)

So the criteria under the Date Scanned field would be
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date

You might want to change the first number in the choose statement to - 5
(Sunday) if you wanted W, Th, F deleted if you run this on Sunday. And you
would need to change the last -2 to -3 if you wanted to delete W, Th, F if you
run this on Saturday.

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

Check out the DateAdd function as it has a weekday setting where it skips
weekends (but not weekday holidays). Something like below

[quoted text clipped - 9 lines]

Format(Date(),"ddd") = Tue
Format(Date(),"dddd") = Tuesday

 

[John Spencer]

Is your Date Scanned field a datetime field or is it a text field that
contains a string that looks like a date?

IF you answer no, can you post the SQL of your query. Open in design view,
select View: SQL from the menu, copy and paste the text.

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

Thanks John! But i'm having some difficulties. I copied:
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date
and pasted it in my criteria. When I run the query it's telling me there is
a "Data Type Mismatch in Criteria Expression"

Do you know how I can fix this?

Kim

Jerry, I'm sorry but using "w" in the DateAdd function gives the same result
as using "d". At least, I've never gotten any other behavior from it.

Start Date
DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-3),Date)

So the criteria under the Date Scanned field would be
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date

You might want to change the first number in the choose statement to - 5
(Sunday) if you wanted W, Th, F deleted if you run this on Sunday. And you
would need to change the last -2 to -3 if you wanted to delete W, Th, F if you
run this on Saturday.

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

Check out the DateAdd function as it has a weekday setting where it skips
weekends (but not weekday holidays). Something like below

[quoted text clipped - 9 lines]

Format(Date(),"ddd") = Tue
Format(Date(),"dddd") = Tuesday

 

[kimb via AccessMonster.com]

I double checked and that field is set as a Date/Time field. That was the
first thing I thought of when I saw the data type mismatch. Here is the SQL
anyway:

DELETE t_finaldownload.[Tracking Number], t_finaldownload.[Package Reference
No 1], t_finaldownload.[Package Reference No 2], t_finaldownload.Weight,
t_finaldownload.Status, t_finaldownload.[Manifest Date], t_finaldownload.
[Scheduled Delivery Date], t_finaldownload.Service, t_finaldownload.[Shipper
Name], t_finaldownload.[Shipper City], t_finaldownload.[Shipper
State/Province], t_finaldownload.[Shipper Country], t_finaldownload.[Ship To
Name], t_finaldownload.[Ship To Address Line 1], t_finaldownload.[Ship To
Address Line 2], t_finaldownload.[Ship To City], t_finaldownload.[Ship To
State/Province], t_finaldownload.[Ship To Postal Code], t_finaldownload.[Ship
To Country], t_finaldownload.[Exception Description], t_finaldownload.[Bill
To Account Number], t_finaldownload.DOW, t_finaldownload.Slic,
t_finaldownload.Building, t_finaldownload.[Date Scanned], t_finaldownload.Day,
t_finaldownload.Time, t_finaldownload.Activity, t_finaldownload.Location
FROM t_finaldownload
WHERE (((t_finaldownload.[Date Scanned])=Date()));

Is your Date Scanned field a datetime field or is it a text field that
contains a string that looks like a date?

IF you answer no, can you post the SQL of your query. Open in design view,
select View: SQL from the menu, copy and paste the text.

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

Thanks John! But i'm having some difficulties. I copied:
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date

[quoted text clipped - 29 lines]

 

[John Spencer]

Is t_FinalDownload a table (and not a query)? If it is a query there is the
possibility that you have done something in the query.

The Delete query can be simplified considerably to just the following.
DELETE
FROM t_finaldownload
WHERE t_finaldownload.[Date Scanned]

=DateAdd("d",Choose(Weekday(Date()),-4,-4,-4,-2,-2,-2,-2),Date())

AND [Date Scanned]<=Date()

If you enter the following into the VBA immediate window what do you get
returned? I get 1/29/2010.
?DateAdd("d",Choose(Weekday(Date()),-4,-4,-4,-2,-2,-2,-2),Date())

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

I double checked and that field is set as a Date/Time field. That was the
first thing I thought of when I saw the data type mismatch. Here is the SQL
anyway:

DELETE t_finaldownload.[Tracking Number], t_finaldownload.[Package Reference
No 1], t_finaldownload.[Package Reference No 2], t_finaldownload.Weight,
t_finaldownload.Status, t_finaldownload.[Manifest Date], t_finaldownload.
[Scheduled Delivery Date], t_finaldownload.Service, t_finaldownload.[Shipper
Name], t_finaldownload.[Shipper City], t_finaldownload.[Shipper
State/Province], t_finaldownload.[Shipper Country], t_finaldownload.[Ship To
Name], t_finaldownload.[Ship To Address Line 1], t_finaldownload.[Ship To
Address Line 2], t_finaldownload.[Ship To City], t_finaldownload.[Ship To
State/Province], t_finaldownload.[Ship To Postal Code], t_finaldownload.[Ship
To Country], t_finaldownload.[Exception Description], t_finaldownload.[Bill
To Account Number], t_finaldownload.DOW, t_finaldownload.Slic,
t_finaldownload.Building, t_finaldownload.[Date Scanned], t_finaldownload.Day,
t_finaldownload.Time, t_finaldownload.Activity, t_finaldownload.Location
FROM t_finaldownload
WHERE (((t_finaldownload.[Date Scanned])=Date()));

Is your Date Scanned field a datetime field or is it a text field that
contains a string that looks like a date?

IF you answer no, can you post the SQL of your query. Open in design view,
select View: SQL from the menu, copy and paste the text.

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

Thanks John! But i'm having some difficulties. I copied:
Between DateAdd("d",Choose(Weekday(Date),-4,-4,-4,-2,-2,-2,-2),Date) AND Date

[quoted text clipped - 29 lines]

Format(Date(),"ddd") = Tue
Format(Date(),"dddd") = Tuesday

 

[kimb via AccessMonster.com]

Thanks John! I think I've got this to work. Sorry for the late reply, I have
been under the weather the past several days.

Thanks again for your help! I really appreciate the time you spent to figure
this out for me.

Kim Bartley

Is t_FinalDownload a table (and not a query)? If it is a query there is the
possibility that you have done something in the query.

The Delete query can be simplified considerably to just the following.
DELETE
FROM t_finaldownload
WHERE t_finaldownload.[Date Scanned]

=DateAdd("d",Choose(Weekday(Date()),-4,-4,-4,-2,-2,-2,-2),Date())

AND [Date Scanned]<=Date()

If you enter the following into the VBA immediate window what do you get
returned? I get 1/29/2010.
?DateAdd("d",Choose(Weekday(Date()),-4,-4,-4,-2,-2,-2,-2),Date())

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

I double checked and that field is set as a Date/Time field. That was the
first thing I thought of when I saw the data type mismatch. Here is the SQL

[quoted text clipped - 32 lines]