Update query leaving some records blank.

[grande]

I have an Update Query that will, every once in awhile, not update two fields (HSBJ_TYPE_ID and DATE_STAMP). HSBJ_TYPE_ID should always be 3, and DATE_STAMP should always be Now().

Here's where it really gets weird.

Not only can HSBJ_TYPE_ID be blank, it's become 2 a couple times. The odd thing is, running the exact same test data through the query will produce different results.

Any help would be greatly appreciated.

SQL Code:

UPDATE HSBJ RIGHT JOIN tblPalmData ON HSBJ.HSBJ_IDENTIFIER = tblPalmData.NewSample_ID SET HSBJ.HSBJ_IDENTIFIER = [tblPalmData].[NewSample_ID], HSBJ.HSBJ_TYPE_ID = 3, HSBJ.ORIGINATING_ORG_ID = 1, HSBJ.DATE_STAMP = Now()
WHERE (((HSBJ.HSBJ_IDENTIFIER) Is Null) AND ((tblPalmData.chk)=0));

Oh yeah, and it's updating HSBJ based on info in tblPalmData, being linked on HSBJ_IDENTIFIER = NewSample_ID.

-------------------------
Just call me Captain Awesome.

 

[PHV]

WHERE (((HSBJ.HSBJ_IDENTIFIER) Is Null)
I think you want an append query instead of an update query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Hmm, that seems to be working, but only time will tell I suppose.

-------------------------
Just call me Captain Awesome.