NeedHelp on IIF statement failing to do what i need in an updtate quer

[Paul7905]

Have an Update Query in which I am attempting to set a field in a table if it finds that a specific field was updated ~ i.e. the query updates the field "Dept".

I added a field to the table that is being updated called "Chg" and in the update query in the "Update to" box I put the following statement (suggested to me by a forum member) ~ it sets a checkmark in the table if there is a difference between the fields ~

my problem is that it works most of the time but not all of the time. when one of the fields (either the updatable field or the record source are null, the query will update the field but the IIF statemend fails to set the Chg field flag)

Example: if AllItemsConsolidated.Dept = null and StoreMisc.Dept = 03 the iif statement will not return -1
but will return -1 if AllItemsConsolidated.Dept = 01 and StoreMisc.Dept = 03.

Does anyone have a recommendation as to how i can make this work ? (i am trying to track what fields were updated by the update query using this method)


IIf([AllItemsConsolidatedTable]![Dept]<>[StoreMisc]![Dept],-1,0) [sig][/sig]

 

[kathryn]

How about using the Nz function, which will turn your Null into &quot;zero, a zero-length string (&quot; &quot, or another specified value&quot; according to the help files.

Something like

IIf(Nz([AllItemsConsolidatedTable]![Dept],&quot;&quot<>Nz([StoreMisc]![Dept],&quot;&quot,-1,0)

Hope that helps. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]