IIf returns #error

[Xray_Chik]

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]=240 Or 241,[UnitDept]![DEPARTMENT_SH_NM],[Correct_Dept_Descr]![Description])



All data types are Short Text.

"L/B Unit Description" column still returns all rows as #error

[isladogs]

Try this

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]=240 Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]=241,[UnitDept]![DEPARTMENT_SH_NM],[Correct_Dept_Descr]![Description])

[Xray_Chik]

@ridders52 Still not working, same #error

[isladogs]

Did you mean the Dept No field is actually text?
If so, enclose 240 and 241 in single quotes like this: '240'

BTW I strongly advise that you get rid of spaces and special characters like '/' & '-' in all table and field names
Similarly not a good idea to use numbers (or a date?) at the start of table names

[Xray_Chik]

Did you mean the Dept No field is actually text?
If so, enclose 240 and 241 in single quotes like this: '240'

BTW I strongly advise that you get rid of spaces and special characters like '/' & '-' in all table and field names
Similarly not a good idea to use numbers (or a date?) at the start of table names

Thank you,
It solved half of the problem, it returns the correct data for deptartments 240 & 241 and all other ones are empty.

[SierraJuliet]

I concur with ridders52.

A description is usually text and not numbers; however, your argument involves numbers that require single quotes.
If that does not work then try replacing "=" with "Like" and see if that fixes the problem.
This is of course assuming you do not have numbers higher than 240 or 241 like 2401 or 2411 because the latter is LIKE the prior.
I had to swap "=" with "Like" before in order to not receive an error.

Is department number 260 blank because you did not specify it or have a description listed in reference table?

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='240' Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='241' Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='260',[UnitDept]![DEPARTMENT_SH_NM],[Correct_Dept_Descr]![Description])

I foresee a problem if you get more department numbers so something like:

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No] Like '2**',[UnitDept]![DEPARTMENT_SH_NM],[Correct_Dept_Descr]![Description])

This is assuming the department numbers will not be greater than or less than the 200 range (i.e. 240, 241, 260).

[isladogs]

It solved half of the problem, it returns the correct data for deptartments 240 & 241 and all other ones are empty.

Check the data in your Description field of the Correct_Dept_Descr table for Dept No = 260 etc
Maybe there are no records for that Dept No
Or perhaps your field datatypes for Dept No do not match in the 2 tables

[SierraJuliet]

If you are able to, I would reconsider naming convention for both the tables and their attributes. It is a little confusing to look at and more so for the next person trying to troubleshoot or program something.

Recommendations:
Remove all spaces, capitalize first letter of each word, and remove dashes, underscores, and slashes. For number as "No" use "Num" as not to confuse someone later, such as "ItemNum" while tables can be "tblNameOfTable"
Also I am not sure because I do not know how it is employed but possibly normalize the main table a little. It appears to contain attributes that may be better organized in their own tables.

Just my two cents.

[Xray_Chik]

Check the data in your Description field of the Correct_Dept_Descr table for Dept No = 260 etc
Maybe there are no records for that Dept No
Or perhaps your field datatypes for Dept No do not match in the 2 tables

data type is "Short Text"in all the tables

[Xray_Chik]

@ridders52 @sierrajuliet
I have multiple dept numbers in table "[01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]"
I am trying to have I column named: "L/B Unit Description" that would return data from "[UnitDept]![DEPARTMENT_SH_NM]" for depts 240 and 241, and "[Correct_Dept_Descr]![Description]" for all other depts.

[isladogs]

I have multiple dept numbers in table "01-01-18 E-HR ITEM CONTROL FINAL"
I am trying to have I column named: "L/B Unit Description" that would return data from "[UnitDept]![DEPARTMENT_SH_NM]" for depts 240 and 241, and "[Correct_Dept_Descr]![Description]" for all other depts.

Yes, that was clear from the start
However, back in post #7, I wrote this:

Check the data in your Description field of the Correct_Dept_Descr table for Dept No = 260 etc
Maybe there are no records for that Dept No

You've said the datatype is Short Text in both tables
So either there is no data for dept 260 or you have spelling errors in your IIf statement
You really do need to sort out all table & field names as both Sierra Juliet & I have already said

[SierraJuliet]

@ridders52 @sierrajuliet
I have multiple dept numbers in table "[01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]"
I am trying to have I column named: "L/B Unit Description" that would return data from "[UnitDept]![DEPARTMENT_SH_NM]" for depts 240 and 241, and "[Correct_Dept_Descr]![Description]" for all other depts.

I was pointing out the question-ability of having a special lookup for just two departments instead of consolidating everything from jump street.

If you have data to retrieve from "[Correct_Dept_Descr]![Description]" and the "[Correct_Dept_Descr]![Description]" data type is the same as all the others then ensure no extra spaces in any names and retype all quotes, commas, etc just to be sure not an encoding error.

[SierraJuliet]

Okay try this if you are still having issues:

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='240' Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='241',[UnitDept]![DEPARTMENT_SH_NM],[01-01-18 E-HR ITEM CONTROL FINAL]![L/B Unit Description]=[Correct_Dept_Descr]![Description])

Also I am assuming in response #10 that "L/B Unit" was changed to "L/B Unit Description" or there is another attribute somewhere in there with "L/B Unit Description" name.

Edited due to typo...whoops

[isladogs]

Sorry Sierra Juliet (SJ for short) but the part in blue is wrong and should be deleted

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='240' Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='241',[UnitDept]![DEPARTMENT_SH_NM],[01-01-18 E-HR ITEM CONTROL FINAL]![L/B Unit Description]=[Correct_Dept_Descr]![Description])

L/B Unit Description is the alias for the query field

In theory, this is correct (assuming the data exists):

L/B Unit Description: IIf([01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='240' Or [01-01-18 E-HR ITEM CONTROL FINAL]![L/B Dept No]='241',[UnitDept]!DEPARTMENT_SH_NM],[Correct_Dept_Descr]![Description])

However its almost impossible to be sure due to the naming 'convention' used

[SierraJuliet]

I just posted the last one in blue because I have seen it elsewhere and it worked, not to mention it is not wrong IIF(expression, true_value, false_value). Without getting into semantics it is like any other programming language IF SomethingIsTrue Then dothis+thatTrue Else dothat+thisFalse Or thisthatconfused End

In the end it was just something to try as part of troubleshooting because in reality Access is a mess and theory does not always apply much like everything else in life. If the original poster tried it and it worked with his/her/their implementation then great, otherwise no harm no foul.

I realized after posting that comment that it was an alias, that is what happens running on 1.5 hours of sleep in 48 hours. I am accustomed to programming out what is happening and not doing it in the table, as they are not always compatible.