Record Select question

[Russ via AccessMonster.com]

I am doing a record select on a table that has a date in one field (field #8)
I want to see if the date is past due (maybe 1 day past today) if it is then
delete the record and provide no Confirmation or error message.

example (maybe?)
if rs.fields(8) = date()-1 then
??Delete only this record??
End if

 

[Dirk Goldgar]

I am doing a record select on a table that has a date in one field
(field #8) I want to see if the date is past due (maybe 1 day past
today) if it is then delete the record and provide no Confirmation or
error message.

example (maybe?)
if rs.fields(8) = date()-1 then
??Delete only this record??
End if

Under what circumstances do you want to do this? I'm not sure what you
mean when you say you're "doing a record select on a table". If you
want to delete all records from some table where the date field is one
day before the current date, you could (at least in theory) execute a
simple delete query:

CurrentDb.Execute _
"DELETE FROM MyTable " & _
"WHERE MyDateField = (Date() - 1)", _
dbFailOnError

That will only display an error if the query is malformed or there's an
error executing it; it won't display a confirmation prompt, nor an
error message if no records are deleted (becuse none meets the
criterion).

 

[Russ via AccessMonster.com]

here is what i did, it is working do you see a problem with it?
'look for old date DB= by date
If rs.Fields(8) <= Date - 1 And rs.Fields(10) = "BD" Then
rs.Delete
End If

I am doing a record select on a table that has a date in one field
(field #8) I want to see if the date is past due (maybe 1 day past

[quoted text clipped - 5 lines]

??Delete only this record??
End if

Under what circumstances do you want to do this? I'm not sure what you
mean when you say you're "doing a record select on a table". If you
want to delete all records from some table where the date field is one
day before the current date, you could (at least in theory) execute a
simple delete query:

CurrentDb.Execute _
"DELETE FROM MyTable " & _
"WHERE MyDateField = (Date() - 1)", _
dbFailOnError

That will only display an error if the query is malformed or there's an
error executing it; it won't display a confirmation prompt, nor an
error message if no records are deleted (becuse none meets the
criterion).

 

[Dirk Goldgar]

here is what i did, it is working do you see a problem with it?
'look for old date DB= by date
If rs.Fields(8) <= Date - 1 And rs.Fields(10) = "BD" Then
rs.Delete
End If

If you want to delete records that are dated before today, it would be
more efficient to say

If rs.Fields(8) < Date And rs.Fields(10) = "BD" Then

Aside from that, I don't see anything technically wrong with the code
you posted. It's safer to refer to fields by their names, though, in
case they get moved around in the table or query on which your recordset
is based. And I don't know the context, so I don't know why you're
looping through a recordset instead of executing a delete query. In
general, running an action query is vastly more efficient, where
circumstances permit it, than looping through a recordset.

 

[Russ via AccessMonster.com]

Thanks, for the help.

FYI..
This is an event / reminder dased database and will run 24/7 I have a timer
that loops through a recordset looking for events that need to be activated.
I just figured that since it is looking in that recordset anyways this would
be a good place to clean up the old data. If you have any suggestions I would
be glad to hear them.

 

[Dirk Goldgar]

Thanks, for the help.

FYI..
This is an event / reminder dased database and will run 24/7 I have a
timer that loops through a recordset looking for events that need to
be activated. I just figured that since it is looking in that
recordset anyways this would be a good place to clean up the old
data. If you have any suggestions I would be glad to hear them.

Not really. In this case you're already working with a recordset
because the application demands it, so your logic makes perfect sense.
The only refinement I'd add, which you may already have in place, is to
open the recordset on a query that extracts only those records that need
to be acted on, one way or another, rather than including a bunch of
records in the recordset that are neither "due" nor expired.

 

[Russ via AccessMonster.com]

Dirk,
Good point, thanks for your help!

Thanks, for the help.

[quoted text clipped - 4 lines]

recordset anyways this would be a good place to clean up the old
data. If you have any suggestions I would be glad to hear them.

Not really. In this case you're already working with a recordset
because the application demands it, so your logic makes perfect sense.
The only refinement I'd add, which you may already have in place, is to
open the recordset on a query that extracts only those records that need
to be acted on, one way or another, rather than including a bunch of
records in the recordset that are neither "due" nor expired.

 

[David C. Holley]

What exactly are the events/reminders that need to be executed and then
events that need to be 'activated'?