Rolling Append & Delete

[Thecherub]

This has probably been answered but I need to do a Rolling Append and Delete to the Month and Day.

I've got the month allright but how do i modify it to account for the Day.

Criteria - "=DateAdd("m",-4,Date())"

Example
as of today anything Nov 12, 03 or before shou;d be removed but i've still got records from Nov 7th, 03 in the Data.

Thank you in advance

 

[Mile-O]

So, your criteria involves everything over 4 months old. What's the rule?

You mention getting the day involved but neglect to mention any rule that governs the selected by the criteria.

 

[dcx693]

The DateSerial function might work better for your criteria:
DateSerial(Year(Date),Month(Date)-4,Day(Date))

Subtracting 4 from the month will adjust the year properly.

 

[Mile-O]

The fact that you say anything on a specific day and before should be removed then you shouldn't use the = sign but use the less than or equal to operator ( <= ).

 

[Thecherub]

Here is the code for the query

INSERT INTO [Data Archive] ( [Employee number], Type, [Date], Comment )
SELECT [Absent Data].[Employee number], [Absent Data].Type, [Absent Data].Date, [Absent Data].Comment
FROM [Absent Data]
WHERE ((([Absent Data].Date)<=DateAdd("m",-4,Date())));

Mile

I need to have anything older than 4 months removed to the archive. so if todays date is april 12, 04 any thing before jan 12, 04 should be archived.

does that explain it?

 

[Mile-O]

Re: Here is the code for the query

INSERT INTO [Data Archive] ( [Employee number], Type, [Date], Comment )
SELECT [Absent Data].[Employee number], [Absent Data].Type, [Absent Data].Date, [Absent Data].Comment
FROM [Absent Data]
WHERE [Absent Data].[Date] <= DateSerial(Year(Date()),Month(Date())-4,Day(Date());

Date, however, is a bad choice for a field name as it's a Reserved Word in Access.

 

[Thecherub]

I did use that

The fact that you say anything on a specific day and before should be removed then you shouldn't use the = sign but use the less than or equal to operator ( <= ).

I did use that my paste somhow excluded that.

the criteria should read "<=DateAdd("m",-4,Date())"

DCX

would the <= work with that date Serial?

 

[Mile-O]

Re: I did use that

would the <= work with that date Serial?

Yes. See Above.

 

[Thecherub]

Data type mismatch

<=DateSerial(Year("Date"),Month("Date")-4,Day("Date"))

Ok i tried that but now i'm getting a Data Type Mismatch, would that be because of the bad field name or the fromat of the feild?

 

[Mile-O]

Functions, even if they have no arguments, need to have their brackets - otherwise the function is misinterpreted by Access in this situation as a typical piece of text.

<=DateSerial(Year(Date()),Month(Date())-4,Day(Date()))

 

[Thecherub]

now I'm stumped

well now i understand what was wrong and I tried it.

No datatype mismatch. but it still will not remove the Nov 1-11 entries. I know i've got at least 2 but the Query Appends 0 rows of data.

should the -4 be inside the () ex. "Month(Date()-4))"

or am I way off the mark?

 

[dcx693]

Whoa. I go away for 15 minutes and there are 100 posts to this thread!

should the -4 be inside the () ex. "Month(Date()-4))"

No. The problem is one of mathematics. If this is February, and you remove four months, you get October. Change the 4 to a 3. Sorry I didn't see that before!

 

[Thecherub]

Duh

I should have seen that my self...

I'm kicking my self

Thanks