IIF Statement?

[NeonSky via AccessMonster.com]

Hello!

My question is simple enough. How would I write the following IIF statement?

If field "A" is null and field "B" is not null then populate field "A" with
data from field "B" and then delete the contents of field "B".

I understand an update query would work as well although I would like to see
it as an IIF statement.

Thanks!!!

 

[Douglas J. Steele]

You can't do that with an IIf statement.

IIf statements are intended to return two values: one if the condition is
true, and one if the condition is false.

It's not intended for multiple actions, as you'd like.

 

[Stefan Hoffmann]

hi,

If field "A" is null and field "B" is not null then populate field "A" with
data from field "B" and then delete the contents of field "B".

UPDATE Table
SET A = IIf(IsNull(A) AND NOT IsNull(B), B, A)
B = IIf(IsNull(A) AND NOT IsNull(B), NULL, B)


mfG
--> stefan <--

 

[NeonSky via AccessMonster.com]

Thanks Douglas,

Thank you for your clarification on IIF statements.

What would be your recommendation to achieve the desired result?

Thanks!

You can't do that with an IIf statement.

IIf statements are intended to return two values: one if the condition is
true, and one if the condition is false.

It's not intended for multiple actions, as you'd like.

Hello!

[quoted text clipped - 10 lines]

Thanks!!!

 

[Douglas J. Steele]

Stefan's showed you one way to do it using 2 IIf statements, but it's still
an Update query.

What exactly are you trying to do?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas,

Thank you for your clarification on IIF statements.

What would be your recommendation to achieve the desired result?

Thanks!

You can't do that with an IIf statement.

IIf statements are intended to return two values: one if the condition is
true, and one if the condition is false.

It's not intended for multiple actions, as you'd like.

Hello!

[quoted text clipped - 10 lines]

Thanks!!!

 

[NeonSky via AccessMonster.com]

Thanks Stefan,

I am doing an export query on a table where I can not modify the data on the
table (thus I can not update), I can only modify the data that is being
exported to an excel doc.....any thoughts?

 

[Stefan Hoffmann]

hi,

I am doing an export query on a table where I can not modify the data on the
table (thus I can not update), I can only modify the data that is being
exported to an excel doc.....any thoughts?

Use the IIf's as columns:

SELECT IIf(IsNull(A) AND NOT IsNull(B), B, A) AS A,
IIf(IsNull(A) AND NOT IsNull(B), NULL, B) AS B
FROM Table


mfG
--> stefan <--

 

[missinglinq via AccessMonster.com]

In your previous post about this you ask for an IF...Then hack:

Private Sub Command4_Click()
If IsNull(Me.A.Value) And Not IsNull(Me.B.Value) Then
Me.A.Value = Me.B.Value
Me.B.Value = ""
End If
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com