IIF Statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NeonSky - 30 Nov 2006 16:06 GMT

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!!!

Reply to this Message

Douglas J. Steele - 30 Nov 2006 16:16 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hello!
>
[quoted text clipped - 10 lines]
>
> Thanks!!!

Reply to this Message

NeonSky - 30 Nov 2006 16:31 GMT

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.
>
[quoted text clipped - 8 lines]
>>
>> Thanks!!!

Reply to this Message

Douglas J. Steele - 30 Nov 2006 16:34 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Douglas,
>
[quoted text clipped - 16 lines]
>>>
>>> Thanks!!!

Reply to this Message

Stefan Hoffmann - 30 Nov 2006 16:24 GMT

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 <--

Reply to this Message

NeonSky - 30 Nov 2006 16:35 GMT

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?

>hi,
>
[quoted text clipped - 6 lines]
>mfG
>--> stefan <--

Reply to this Message

Stefan Hoffmann - 30 Nov 2006 16:43 GMT

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 <--

Reply to this Message

missinglinq - 30 Nov 2006 16:43 GMT

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

Signature

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

Answers/posts based on Access 2000

Reply to this Message