[X]My Assistant

[rramsey]

I'm running a query to compile data from 2 columns into one. I have the following columns.
A [HomeNum] Column that holds an employees home department number.
A [TransNum] Column that holds the department number for a department they transferred to.
The [TransNum] Column only has when they transfer and all the other boxes are blank, so I wanted to make a [DeptNum] Column using an IIf statement to put the home number as default unless the transfer number was used.

I've tried the following statement- DeptNum: IIf([HomeNum]<>[TransNum],[TransNum],[HomeNum])

This successfully give the [HomeNum] in the blanks, but where it is supposed to fill in the [TransNum] it returns that field as "#Error"

I've attached an example of the result for reference. Thanks!
This post has been edited by rramsey: Nov 13 2019, 02:00 PM

Attached File(s)

 _Error.png ( 67.01K )Number of downloads: 6

 

[theDBguy]

Hi. I think the error is because you may have a data type mismatch. What do you get with this expression instead?

CODE

Nz([TransNum],[HomeNum])

[rramsey]

That worked perfectly! Thank you, I hadn't even thought of that I've been starting at this for too long!

[theDBguy]

Hi. Glad to hear it worked for you. Good luck with your project.