IIF statements where Is Not Null

[carmenv323]

Hello, I have the following on a form to update a field on a form, but I want to make it an Update Query instead. I've tried using the Builder option but haven't been successful. Any ideas on how to translate this in a query?



If IsNull(Me.Underground) And IsNull(Me.Overhead) Then
Me.SCOPECAT = Me.Station
ElseIf IsNull(Me.Station) And IsNull(Me.Overhead) Then
Me.SCOPECAT = Me.Underground
ElseIf IsNull(Me.Station) And IsNull(Me.Underground) Then
Me.SCOPECAT = Me.Overhead
End If

[pbaldy]

I think you have a design problem, but you could use nested IIf() functions. Are two of those fields always Null? If so, there should probably be a single field.

[carmenv323]

There is just one field in the table that I want to append but I used a MS Form and I could only use the three options to branch out, now I need to add it to the correct field only if it's not null.

I created three separate tables for each of the options and working on trying to nest but it's still the same issue where I don't get any results

[June7]

If you try to run an UPDATE action SQL on a record that is already in edit mode on form, it will trigger conflict error.

Why save SCOPECAT to table? Its value is dependent on other data and can be calculated when needed.

Why do you want to use UPDATE action SQL?

Not really understanding the schema. If you want to provide db for review, follow instructions at bottom of my post.

[pbaldy]

Posting the db may reveal design issues that will plaque you down the road. Better to fix them early than live with them forever.

If the design is set, did you try the nested IIf()?