If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append record with Yes/No
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 |
#2
|
|||
|
|||
Append record with Yes/No
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 "Nick" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|