An update query except for certain things?

[Darla]

Hello everyone! I have an update query that takes the values from Field1 in my table and populates them into Field2. The only problem is that I need to exclude any values that are "NA" or "TBD" from being copied over to Field2. I see where I can put positive criteria in the query, but not a negative. What is the best way to accomplish this?

[pbaldy]

Try

<>"NA" And <> "TBD"

[Darla]

Try

<>"NA" And <> "TBD"

This didn't work for me, when I add this criteria in, it doesn't update anything

[Bulzie]

Can you open query in SQL design mode and paste the SQL code for it? What pbaldy wrote should work for you.

[Darla]

This is what I have:


UPDATE Table1 SET Table1.Field2 = Field1
WHERE (((Table1.Field2)<>"NA" And (Table1.Field2)<>"TBD"));

[ItsMe]

Oh, it seems you have criteria for the same column. In this case the OR operator is likely what you are after.
WHERE (((Table1.Field2)<>"NA" OR (Table1.Field2)<>"TBD"));

[Darla]

Thank you!!!!!!!!!!!!!!!

[Bulzie]

Did that work? I did small test and OR did not work. When using <> with 2 criteria you have to use AND. What am I missing?

[ItsMe]

Did that work? I did small test and OR did not work. When using <> with 2 criteria you have to use AND. What am I missing?

I did not test it or even give it much thought. I looked at the same field names and the AND. At that point a buzzer and a circle dash flashed in my view. Then I posted.

[pbaldy]

I'll also be surprised if it works with Or. Nothing would be excluded from the update.

[Bulzie]

I'll also be surprised if it works with Or. Nothing would be excluded from the update.

This. In my tests using OR updated all the records because those 2 conditions are exclusive if using <> with OR. Oh well if it works it works!!!

[ItsMe]

... Oh well if it works it works!!!

Just because something happened, does not mean it works.

[ItsMe]

It seems the issue with post #5 is the fact there is not a value provided that will be used to process the UPDATE. In other words, a JOIN is missing or a parameter is missing.

For example, this statement requires a JOIN on ID1

UPDATE TableTest1 INNER JOIN TableTest2 ON TableTest1.ID1 = TableTest2.ID1 SET TableTest2.Field2 = [TableTest1]![Field2]
WHERE (((TableTest2.Field2)<>"NA" AND (TableTest2.Field2)<>"TBD"));

[Bulzie]

No both fields are in same table so no joins, just criteria to exclude records from the update.

[ItsMe]

No both fields are in same table so no joins, just criteria to exclude records from the update.

OK, I see that now.
= Field1

Not sure what caused the original issue then.

.