IF Statement in an Update QRY 1

[davo]

Hi,<br><br>I am trying to change a value in a record if one of two conditions holds true. I can use a&nbsp;&nbsp;select query to get this to work yet not an update query.<br><br>Any suggestions? Here is the code that works for the select query.<br><br>SELECT trans.TRANSTYPE, TBLbrokers_table.broker, iif(<br>(((Trans.TRANSTYPE)=&quot;buy&quot AND ((TBLbrokers_table.CA)=Yes) AND ((TBLbrokers_table.Buy_charge)=YES))=true,<br>'CABILL',iif(<br><br>(((Trans.TRANSTYPE)=&quot;cbuy&quot AND ((TBLbrokers_table.CA)=Yes) AND ((TBLbrokers_table.Buy_charge)=YES))=true,<br>'CACXL','')) AS CAType<br>FROM Trans INNER JOIN TBLbrokers_table ON Trans.BROKER = TBLbrokers_table.Broker_code;

 

[Cornerstone]

I am having a little trouble understanding your intent but why not try this:<br><br>Create a procedure.<br><br>Dim db as database<br>Dim rst as recordset<br><br>Set db=currentdb<br>Set rst=openrecordset(&quot;yourtableorqueryname&quot;, dbopendynaset)<br><br>rst.movefirst<br>do until rst.eof<br>&nbsp;if condition=result or condition=result2 then&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;rst.edit<br>&nbsp;&nbsp;&nbsp;rst!fieldwhatever=yourchange<br>&nbsp;&nbsp;&nbsp;rst.update<br>&nbsp;end if<br>rst.movenext<br>loop<br><br>rst.close<br>db.close<br>

 

[davo]

Cornerstone,

Sorry the above code is SQL for MS Access97. I have to join two tables first.

I have found that the macros are too slow given the size of the dataset.

I think I will use the results of the select query in an update Query.

Thanks