Home > IT Answers > Database > DLookup or IIF statement?
Alexvalentina
10 pts.
 DLookup or IIF statement?
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
ASKED: October 11, 2010  6:19 PM
UPDATED: October 12, 2010  8:48 PM
RELATED QUESTIONS
Answer Wiki:
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>
Last Wiki Answer Submitted:  October 12, 2010  8:43 pm  by  mgj   320 pts.
All Answer Wiki Contributors:  mgj   320 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

mgj
 320 pts.