Update query not working

[sarahb845]

Help....

I have what I thought was a very simple update query - take one field in a table and move it to another field in the same table, under certain conditions. It returns the correct number of records, but the field is blank.

Move LastReviewDate to AnnualReview if LastReviewType = "A"


Here is the SQL:
UPDATE tblPerformanceMgmt SET tblPerformanceMgmt.AnnualReview = [tblPerformanceMgmt].[LastReviewDate]
WHERE (((tblPerformanceMgmt.LastReviewType)="A"));


What am I doing wrong???

 

[BLeslie88]

Suggestion...

Verify that both fields are same "types" meaning dates ect......

Ensure Field2 indeed has dates....

If so try the Field1 = nz(Field2,"15-Sep-03") to see if if the date you specified is written where = "A" to ensure the criteria works....

Regards,

 

[sarahb845]

1. Both fields are Date/Time datatype.

2. Field2 (i.e. LastReviewDate) has data in the records. However, some of the records are NULL. Does that make a difference????

------------------
You Said:
If so try the Field1 = nz(Field2,"15-Sep-03") to see if if the date you specified is written where = "A" to ensure the criteria works....
------------------

I have no idea what this means. Can you explain this???

 

[BLeslie88]

What I mean is nz(fld1,x) is a function.. it check to see if fld1 is null if it is it assigns x instead, so in your case it would assign a date of your choice to the tables.... AnnualReview would be assigned x where LastReviewDate = NULL & AnnualReview would be assigned LastReviewDate where it had values....

Hope this helps....

 

[sarahb845]

Ok, more questions.

Is this how my SQL statement should look????

UPDATE tblPerformanceMgmt SET tblPerformanceMgmt.AnnualReview = nz([tblPerformanceMgmt].[LastReviewDate], Null)
WHERE (((tblPerformanceMgmt.LastReviewType)="A"));


But, I'm not sure if I really need to use this function, because the LastReviewType will ALWAYS have a corresponding LastReviewDate.

This seems like such a basic query. I just don't understand. I've been playing with this query for 24 hours now, and am stuck until I can figure it out!

 

[sarahb845]

Tried the sql in previous post, with the nz(field1,x) function. All returned records still come back blank.

The WHERE condition is working (# of records are reduced by the LastReviewType = 'A".) But, the rest is not........

 

[sarahb845]

Works!

So - the query does work afterall. I was switching between the design view and datasheet view to make sure that the data was showing up in the AnnualReview column. It wasn't, so I assumed there was something wrong with the query. I really did NOT want to execute the query until I knew that the data was showing correctly in the datasheet view.

But, I finally decided to just execute it, and see what happens. And, it worked.

So, why didn't my data show up in the datasheet view of the query? I thought that was the whole purpose of having that view - to view the data BEFORE updating!!!!!!!

 

[BLeslie88]

She did have some NULL values where she may have wanted assigned dates Pat...

Also for further reference so you aren't scared to try something, as I still do after all these years... Just work on a backup.... If all your coding works well, just import those object changed into your original....

 

[sarahb845]

Oh, good, thanks for the tip on the nz() function. I really didn't see the purpose under the current circumstances.

But, I'll probably use it in the future! I don't know how many if/then statements I've done concerning null values!!