Running Update Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bunky - 27 Feb 2007 16:21 GMT

I have a table that has sensitive data on it and Management wants me to
delete that piece of data after seven days have elapsed. I have made a
select query to do what I want but when I change to an Update query I get
messages indicating my expressions that I am using are incorrect. The
expression I have written is
exp1r: IIf([Processing Date]+8>Now(),'0',[cc number]). When I select the
update query option and try to run I get:
'IIf([Processing Date]+8>Now(),'0',[cc number])' is not a valid name. Make
sure that it does not include invalid characters or punctuation and that it
is not too long.'

I need to have this update run whenever a certain form is opened and was
making a macro to do the execution of the query. At least that was the plan.
How can I accomplish this?

Thanks!
Kent

Reply to this Message

George Nicholson - 27 Feb 2007 16:59 GMT

Don't think iif() will work in an UPDATE since it won't change any
underlying data. It does affect what gets displayed in SELECT query results
but an Update query doesn't return any records to display.

UPDATE MyTable
SET [CC Number] = 0
WHERE ([Processing Date]+8>Now;

HTH,

>I have a table that has sensitive data on it and Management wants me to
> delete that piece of data after seven days have elapsed. I have made a
[quoted text clipped - 16 lines]
> Thanks!
> Kent

Reply to this Message

Jerry Whittle - 27 Feb 2007 17:13 GMT

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

I'll take a WAG at it without the above data. In the criteria for the
Processing Date field put < Now() - 8

Run it as a select query and see if it returns the correct records. If so,
change it to an update query and put 0 in the Update To under the cc number
field..

Of course test the above in a copy of the table or entire database first.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a table that has sensitive data on it and Management wants me to
> delete that piece of data after seven days have elapsed. I have made a
[quoted text clipped - 13 lines]
> Thanks!
> Kent

Reply to this Message

Bunky - 28 Feb 2007 14:55 GMT

Here is the SQL that was requested:
SELECT Cancellation_New.[CC Number], Cancellation_New.[CC Expire Date],
Cancellation_New.[Processing Date], IIf([Processing
Date]+8>Now(),'0000000000000000',[cc number]) AS exp1r, IIf([Processing
Date]+8>Now(),'0000',[CC Expire Date]) AS expr2
FROM Cancellation_New;
I was experimenting some so let me know what you think.

Thanks,
Kent

> Show us the SQL. Open the query in design view. Next go to View, SQL View
> and copy and past it here.
[quoted text clipped - 25 lines]
> > Thanks!
> > Kent

Reply to this Message