Update Query Help

[Andy]

I have an update query. For one unique customer, there are two records in the
table that provides the updated field, one has values and one is NULL.
Problem being that when I update, it first updates with the field with a
value and then updates with the NULL so that when I look at the updated
table, the updated field reads NULL. I can't seem to exclude NULL fields from
the update?

UPDATE [t_Tertiary Referral Clock Stops with Appt Details] RIGHT JOIN
[t_Clock Stop Flatfile] ON [t_Tertiary Referral Clock Stops with Appt
Details].PathwayID = [t_Clock Stop Flatfile].PathwayID SET [t_Clock Stop
Flatfile].[NACS Code?] = [t_Tertiary Referral Clock Stops with Appt
Details]![NACS Code?]
WHERE ((([t_Tertiary Referral Clock Stops with Appt Details]![NACS Code?])
Is Not Null));

 

[KARL DEWEY]

It might not be Null but a zero lenght string, there is a difference.
Try this --
WHERE [t_Tertiary Referral Clock Stops with Appt Details]![NACS Code?]
Is Not Null AND [t_Tertiary Referral Clock Stops with Appt Details]![NACS
Code?] <> "";

 

[Andy]

Wasn't aware that NULL is different to "". Seemed to work, thanks. Is there
anywhere you can recommend that I can read up on this difference?

Thanks again

It might not be Null but a zero lenght string, there is a difference.
Try this --
WHERE [t_Tertiary Referral Clock Stops with Appt Details]![NACS Code?]
Is Not Null AND [t_Tertiary Referral Clock Stops with Appt Details]![NACS
Code?] <> "";

--
KARL DEWEY
Build a little - Test a little

I have an update query. For one unique customer, there are two records in the
table that provides the updated field, one has values and one is NULL.
Problem being that when I update, it first updates with the field with a
value and then updates with the NULL so that when I look at the updated
table, the updated field reads NULL. I can't seem to exclude NULL fields from
the update?

UPDATE [t_Tertiary Referral Clock Stops with Appt Details] RIGHT JOIN
[t_Clock Stop Flatfile] ON [t_Tertiary Referral Clock Stops with Appt
Details].PathwayID = [t_Clock Stop Flatfile].PathwayID SET [t_Clock Stop
Flatfile].[NACS Code?] = [t_Tertiary Referral Clock Stops with Appt
Details]![NACS Code?]
WHERE ((([t_Tertiary Referral Clock Stops with Appt Details]![NACS Code?])
Is Not Null));