Changing data depending on time?

  • Thread starter Thread starter tomasz.cichon
  • Start date Start date
T

tomasz.cichon

Hi, everyone:

Does anyone know if it is possible to change data in a table according
to a date?
Let's say I would like to change the contents of all fields that are
two days old and I would like to Access to do the work. Can Access
actually do the check/change automatically?

Thanks a lot,

Tom
 
Hi, everyone:

Does anyone know if it is possible to change data in a table according
to a date?
Let's say I would like to change the contents of all fields that are
two days old and I would like to Access to do the work. Can Access
actually do the check/change automatically?
Click to expand...

You can certainly run an Update query using a criterion

<= DateAdd("d", -2, Date())

to select all records where a date/time field is older than two days... but
the need to do this makes me VERY suspicious of your table design. Data in
records should NOT depend on the date; what if nobody happens to open the
database on the day when data should change!?

Could you explain the nature of this table; what fields you want to change;
and *why*?

John W. Vinson [MVP]
 
You can certainly run an Update query using a criterion

<= DateAdd("d", -2, Date())

to select all records where a date/time field is older than two days... but
the need to do this makes me VERY suspicious of your table design. Data in
records should NOT depend on the date; what if nobody happens to open the
database on the day when data should change!?

Could you explain the nature of this table; what fields you want to change;
and *why*?

John W. Vinson [MVP]
Click to expand...

Thanks for the tip about "Update Queries." This looks like what I
need.

One of the fields in the database contains credit card numbers and I
want those numbers to be visible only for 48 hours. After that, the
number will change to something like **** **** **** 1234. The
database will be open by users almost every day of the year.
Occasionally, when users don't open it, the "update query" will run
the following day.

Tomasz Cichon
VANCOUVER, BC
 
Thanks for the tip about "Update Queries." This looks like what I
need.

One of the fields in the database contains credit card numbers and I
want those numbers to be visible only for 48 hours. After that, the
number will change to something like **** **** **** 1234. The
database will be open by users almost every day of the year.
Occasionally, when users don't open it, the "update query" will run
the following day.

Tomasz Cichon
VANCOUVER, BC
Click to expand...

It works!!

UPDATE
SET
.CC = "****************"
WHERE (((TABLE.DATE)<=DateAdd("d",-2,Date())));

The update query runs every time the form opens.
Thanks again, John.
 
UPDATE
SET
.CC = "****************"
WHERE (((TABLE.DATE)<=DateAdd("d",-2,Date())));

The update query runs every time the form opens.
Thanks again, John.
Click to expand...


If that's really what you want to do, fine.

The two problems that I see with it is that this will update EVERY record
older than two days old, including all the record that were updated last week,
last month, or last year. This will cause your database to bloat rapidly
because you'll be "changing" every record in the table (even though it's a
non-change change) every day, or even multiple times each day.

I'd suggest adding a criterion to exclude already updated records:

UPDATE
SET
.CC = "************" & Right([CC], 4)
WHERE
.[DATE] <= DateAdd("d", -2, Date())
AND CC NOT LIKE "[*]*"

This will leave the last four digits in the number, as requested in your text
message; and it will only update records which *don't* start with an asterisk.

John W. Vinson [MVP]
 
The update query runs every time the form opens.
Click to expand...

ah... the other problem I see (which I neglected to type) is that the CC value
will be irretrievably destroyed. You won't be able to find out what was there,
even if some customer calls up and says "Did you ever have a charge against
Visa card 3333-3333-3333-3333?"

John W. Vinson [MVP]
 
ah... the other problem I see (which I neglected to type) is that the CC value
will be irretrievably destroyed. You won't be able to find out what was there,
even if some customer calls up and says "Did you ever have a charge against
Visa card 3333-3333-3333-3333?"

John W. Vinson [MVP]
Click to expand...

John, you answered the questions before I even asked them! Thank you
so much for your help. You are right - changing the changed fields
would be an unnecessary redundancy. Thanks for the hint on how to
keep the last four digits from the card #.
 
Back
Top