DLookup or IIF statement?

10 pts.
Tags:
DLookup
IIF
IIF function
IIF statement
How do I update the value of one row with the value of a different row? i want to build an expression

For example: I have 3 columns. If Column 1=C and C is Null, then I want the values for C in Column 2 to = 10000 (the value from Column 2 for A) and the values for C in Column 3 to = 22000 9the value from Column 3 for B)

Column1   Column2   Column 3

A              10000        200000

B              12000        220000

C

D              16000       300000

EXPECTED RESULT:

Column1   Column2   Column 3

A              10000        200000

B              12000        220000

[strong]C             10000         220000[/strong]

D              16000       300000



Software/Hardware used:
Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

Do it as one SQL update statement.
If both are always null, this will work.
If not then split it up accordingly.
NOTE: The dots after the ] bracket are critical.
Its not going to work without them.
This was tested in msAccess 2003

<pre>UPDATE tbl3Columns,
[SELECT Col2 FROM tbl3Columns WHERE Col1=’A'; ]. AS [tblSourceCol2 ],
[SELECT Col3 FROM tbl3Columns WHERE Col1=’B'; ]. AS [tblSourceCol3 ]
SET tbl3Columns.Col2 = tblSourceCol2.Col2, tbl3Columns.Col3 = tblSourceCol3.Col3
WHERE tbl3Columns.Col2 IS NULL;</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • mgj
    OOPS IF BOTH ARE NULL THAT WOULD BE
    WHERE tbl3Columns.Col2 IS NULL AND tbl3Columns.Col3 IS NULL;
    But not sure if your needs are both always null or sometimes yes sometime no.
    320 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following