Update is deleting current data from field...

[bcp1961]

I am very new to Access and would really appreciate some help with this....

I have an Update Query:
(both SentLetter and over2yrs fields are in test3 table)

UPDATE test3 SET test3.SentLetter = IIf([over2yrs]<=Format(Now(),"mm/dd/yyyy")
AND ([SentLetter] Is Null),Date(),IIf([over2yrs]>Format(Now(),"mm/dd/yyyy"),
""));

I am trying to update the SentLetter field with today's date when the
over2yrs field is <= today's date and also the SentLetter field is empty.

I ran the query and it filled in the appropriate records with today's date. I
then changed the pc date to tomorrows date and added records to be filled in
with "tomorrows date", but the query deletes the date in the SentLetter field
that was inserted by the first run (yesterday's date), while entering the new
pc date into the fields where there had been no date and met the over2yr
criteria.

This query is to be run once month and a report will be generated for letters
to be sent out.

Any ideas on what is wrong with my query?

Bernie

 

[Rick Brandt]

I am very new to Access and would really appreciate some help with
this....

I have an Update Query:
(both SentLetter and over2yrs fields are in test3 table)

UPDATE test3 SET test3.SentLetter =
IIf([over2yrs]<=Format(Now(),"mm/dd/yyyy") AND ([SentLetter] Is
Null),Date(),IIf([over2yrs]>Format(Now(),"mm/dd/yyyy"), ""));

I am trying to update the SentLetter field with today's date when the
over2yrs field is <= today's date and also the SentLetter field is
empty.

I ran the query and it filled in the appropriate records with today's
date. I then changed the pc date to tomorrows date and added records
to be filled in with "tomorrows date", but the query deletes the date
in the SentLetter field that was inserted by the first run
(yesterday's date), while entering the new pc date into the fields
where there had been no date and met the over2yr criteria.

This query is to be run once month and a report will be generated for
letters to be sent out.

Any ideas on what is wrong with my query?

Bernie

If you don't want to update all rows then you need a WHERE clause to apply
criteria and limit the rows affected. Your current query has none.

 

[bcp1961]

Thanks! Rewrote the update query as follows:

UPDATE test3
SET test3.SentLetter = Date()
WHERE test3.over2yrs<=Format(Now(),"mm/dd/yyyy") AND ([SentLetter] Is Null);

Seems to be working like a charm!