Show IIF result in Report

[xyba]

I have the following IIF statement in an unbound textbox on a form but want show the result in a report for each relevant record but don't know how to do this or if it's possible. Can anyone please advise?

=IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1))

 

[arnelgp]

include this in your query and set your report's recordsource to that query.
add the calculated field from the above query to your report.

 

[xyba]

include this in your query and set your report's recordsource to that query.
add the calculated field from the above query to your report.

How do I include it in my query?

 

[arnelgp]

create a new query based on your table.

select id,..., IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) &
 ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) &
 ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] &
 ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1) AS [COLOR=Blue]LastRoute[/COLOR] 
From your Table;

 

[xyba]

create a new query based on your table.

select id,..., IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) &
 ("/"+[dept4]),"/")=0,[dept1],Mid([dept1] & ("/"+[dept2]) &
 ("/"+[dept3]) & ("/"+[dept4]),InStrRev([dept1] &
 ("/"+[dept2]) & ("/"+[dept3]) & ("/"+[dept4]),"/")+1) AS [COLOR=blue]LastRoute[/COLOR] 
From your Table;

That's giving me an error - "Syntax error (missing operator) in expression "...""
Do I need to rename any other part of the expression except for the table name in the From statement?

 

[arnelgp]

select IIf(InStr(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], "/") = 0, [dept1], Left(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], InStr(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], "/") - 1))  As LastRoute from yourTable;

 

[xyba]

select IIf(InStr(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], "/") = 0, [dept1], Left(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], InStr(([dept4] + "/") & ([dept3] + "/") & ([dept2] + "/") & [dept1], "/") - 1))  As LastRoute from yourTable;

The field references you've quoted are different so I've had to rename some and add some in and my code is below but I'm till getting a syntax error

SELECT IIf(InStr(([MainDept] + "/") & ([Referral1] + "/") & ([Referral2] + "/") & ([Referral3], "/") & ([Referral4], "/") & ([Referral5], "/") = 0, [MainDept], Left(([Referral5] + "/") & ([Referral4] + "/") & ([Referral3] + "/" & ([Referral2] + "/") & ([Referral1] + "/")) & [MainDept], InStr(([Referral5] + "/") & ([Referral4] + "/") & ([Referral3] + "/") & ([Referral2] + "/") & ([Referral1] + "/") & [MainDept], "/") - 1)) As LastRoute FROM Documents;

 

[xyba]

I've resolved it. Thanks for your help.