Re: SQL conditional UPDATE

Sorry, my question was a bit unclear. I now have no problem with using IIF in SQL, (I'd originally just forgotten about the existence of the command and of course MS Help wouldn't find any reference to using conditions). What I want to do with IIF is internal to Access - I want to make the default value of a field conditional on the contents of another field. I'm no Access expert but had thought I should be able to use IIF in "default value" in the field's properties on table design. It allows me to use IIF with values that are constant (no use whatsoever I would have thought) but I cannot reference fields either within that table or any other one. I know making calculated fields part of the database's main design is bad practice and against normalisation rules but it's just the easiest way for this problem. Any ideas? Derek ----- Original Message ----- From: "Bob Hall" <bobhall@clark.net> To: "Derek Paterson" <d.paterson@craneware.com> Cc: <php-db@lists.php.net> Sent: Saturday, October 14, 2000 4:22 AM Subject: Re: [PHP-DB] SQL conditional UPDATE > >The syntax is "UPDATE table SET field1 = (IIF (condition,valuea,valueb)) > >WHERE someothercondition;" > > > >Now I'm stuck trying to use fields in the default rule - IIF works in > >default rules > >eg IIF(1<2,"egg","chicken") > >but as soon as you include a field > >eg IIF([whichfirst]>1,"egg","chicken") it spits out... > > > >"The database engine does not recognize either the field <name> in a > >validation expression, or the default value in the table <name>. (Error > >3344) > >Your expression refers to a field incorrectly, or to a field that does not > >exist. Check to make sure the field name is valid and spelled correctly." > > > >According to the help screens I've looked at you should be able to reference > >fields, I've tried [field], "[field]", [table].[field], [table]![field] all > >to no avail. It is definitely spelled correctly btw. > > I've used fields in Iif in Access SQL statements fairly often without > many problems. Can you post the SQL statement and the table > definition? > > >Derek > >{giving consideration to the aerodynamic properties of a PC} > > Forget it. WinTel machines make better anchors than projectiles. > > Know thyself? Absurd direction! > Bubbles bear no introspection. -Khushhal Khan Khatak