Append record with Yes/No

[Nick]

I have some records that occasionally need to have fields updated that change
the field it is looking and also the clear another field.
I am sure it is an append query but I am not sure how to write it. The is
the criteria to update field1 and field2. IIf Field1 is "Yes" then append
Field1 to "No" and clear contents in Field2 with a message stating "Records
have been updated" Else "There are no records to update"
Thanks

 

[vanderghast]

INSERT INTO tableName( field1, field2)
SELECT 0, Null
FROM tablename
WHERE field1




will append as many records are there are, actually, with field1 = yes.

I assumed you mean yes, or true, not the string "yes". If you really mean
the strings "yes" and "no", then it is more like:

INSERT INTO tableName( field1, field2)
SELECT "no", Null
FROM tablename
WHERE field1 = "yes"



Note that nothing forbid to run that query many times, so you would end up
with multiple records appended, each time, but I assume you presented only a
skeleton of the problem you try to solve and that is maybe why it seems to
make more or less sense.


As for the messages, that would be done when using a form. You can count the
number of fields where field1 is "yes" :

DCount("*", "tableName", "field1" )

or

DCount("*", "tableName", "field1 = ""yes""" )

if you really mean the string "yes". If the result is zero, no record would
be appended by the previously supplied query. So it is up to you to test
that DCount and to supply the appropriate message, from under the button
click of your form (button which proposes the end user to start the whole
scenario).



Vanderghast, Access MVP