Forum Discussion
Data import from Access incorrect IIf processing
We have a very simple query in access which has an expr with an IIf statement.
The query processing in Access is correct, but when imported to Excel, it's processed wrong with the wrong results.
The results in Access are correct, but when excel is processing it, it's only returning the else result.
I know this can be achieved another way, but I'd like to know why this is doing that and if it can be fixed as is
Found the solution to my own problem.
Query needs to be in ANSI 92 syntax to work on both platforms. Excel power query doesn't see * as a wild card, it needs %
Replacing * with % will help it work in excel but not in Access
Replacing the syntax in full like this Expr3: IIf([Field1] ALike "Rt%","-1","2") resolves on both
I hope this helps someone
1 Reply
Found the solution to my own problem.
Query needs to be in ANSI 92 syntax to work on both platforms. Excel power query doesn't see * as a wild card, it needs %
Replacing * with % will help it work in excel but not in Access
Replacing the syntax in full like this Expr3: IIf([Field1] ALike "Rt%","-1","2") resolves on both
I hope this helps someone