Running Update Query

[Guest]

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

 

[George Nicholson]

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,

 

[Guest]

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.

 

[Guest]

Jerry,

I tried your solution and It WORKED BEAUTIFULLY!!!! Thank you so much!

Kent

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.

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.
--
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
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

 

[Guest]

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.

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.
--
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
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