Replacing a subset of data in one column with text in another column.

[DXZ08]

Hi,
What would be the best way to handle this issue:

In a table, I have two columns with text data.
Column 1 is titled "Unit" and contains a selected text from a Form drop down which contains "OTHER" as one of the drop down choices.
Column 2 is titled "If OTHER, specify" and as you likely have guessed it already, contains free text entries ONLY for those rows that have "OTHER" in Column 1. Column 2 is otherwise blank for all other records.

To clean up the report I'm making and have the data only in one column instead of two, I'd like to move the free text from Column 2 into Column 1 via query but only for those records where Column 1 contains word "OTHER".
The query should still contain all of the original records, I'm not trying to filter it by those records.

Something along the lines of: Replace "OTHER" in Column 1 with text from Column 2 but only for those rows where Column 1 text = "OTHER". Leave the other Column 1 data untouched.

Thanks,

 

[SydneyGeek]

Instead of rewriting the data, try biulding an expression to use in the report. Something like...

SelectedUnit:Iif([Field1]="Other",[Field2],[Field1])

Denis