date criteria in an update query

B

BigG

I am new to this so please bare with me.
I have a DB where I want to calculate members who have not ordered an item
in the last 2 months and then delete them from the DB. I believe than an
update query is best? and the criteria should be something like
=DateAdd("m",-2,Date())
Click to expand...
this does not return the correct records, can anyone help me?
 
F

Frank Stone

hi,
an update query changes data that exists in a db. If you
want to delete members then you should use a delete query.
for criteria try this:
<=DateAdd("m",-2,now())
or
<=Now()-60
both will work. tested.
good luck
Frank
 
A

Arvin Meyer

You need a delete query to delete, not an update query, The menu items will
provide the syntax for you, just get your Select query to return the records
you want. Your syntax for criteria, seems correct.

You may want to reconsider deleting members who have not ordered in the last
2 months. If your database is designed correctly, with proper referential
integrity enforced, you will not be able to delete any of them that have any
orders at all. And assuming you could, what do you do with orders which have
no person connected to them?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
D

Dave

Further to Arvin's excellent advice about not deleting inactive members, you
might instead want to use an "Active" field (Y/N) in your members table that
will allow you to ignore these folks when you want to, or to select them
when you want that (such as to prod them to buy something!) You can then
update the Active field as you need to, using the criteria you describe --
it looks right.
 
Top