UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Iif Statement Only Returns The Falsepart., Access 2016    
 
   
rramsey
post Nov 13 2019, 02:00 PM
Post#1



Posts: 4
Joined: 3-September 19



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)
Attached File  _Error.png ( 67.01K )Number of downloads: 6
 
Go to the top of the page
 
theDBguy
post Nov 13 2019, 02:05 PM
Post#2


UA Moderator
Posts: 76,911
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Nov 13 2019, 02:21 PM
Post#3



Posts: 4
Joined: 3-September 19



That worked perfectly! Thank you, I hadn't even thought of that pullhair.gif I've been starting at this for too long!
Go to the top of the page
 
theDBguy
post Nov 13 2019, 02:23 PM
Post#4


UA Moderator
Posts: 76,911
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th December 2019 - 07:26 AM