Expression too Complex Error while using complicated IIF Condition Statements- Access SQL
Im getting a query error in MS Access "Expression too Complex in query expression". I have a bunch of nested IIF statements with complex Conditional Statements, i have about 23 of these total and currently im on 13 when it started breaking.
A common resolution i've seen is to create a seperate lookup table housing the values to be returned but i dont think this will work in my case since i have complex boolean logic.
I've researched the issue and i believe using SWITCH statement will cause the same issue.
Someone from another post suggested to break the IIF statement into 2 parts and then a 3rd final IIF to compare the two but havent given it a shot yet.
Expression Too Complex In Access 2007
iif( (HedgeFile.[UnwindDate] is Not Null OR HedgeFile.[UnwindDate] <> '') AND (HedgeFile.[UnwindDate] <= 12/31/2018 AND HedgeFile.[Current Base Rate] = 0), '2. Terminated',
iif (HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
iif (HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK') AS FilterName
If anyone has any advice on how to tackle this, please advise. Looking forward to hearing from you!
sql ms-access switch-statement iif
asked Dec 28 '18 at 16:27
MannyManny
44
|
show 1 more comment
Im getting a query error in MS Access "Expression too Complex in query expression". I have a bunch of nested IIF statements with complex Conditional Statements, i have about 23 of these total and currently im on 13 when it started breaking.
A common resolution i've seen is to create a seperate lookup table housing the values to be returned but i dont think this will work in my case since i have complex boolean logic.
I've researched the issue and i believe using SWITCH statement will cause the same issue.
Someone from another post suggested to break the IIF statement into 2 parts and then a 3rd final IIF to compare the two but havent given it a shot yet.
Expression Too Complex In Access 2007
iif( (HedgeFile.[UnwindDate] is Not Null OR HedgeFile.[UnwindDate] <> '') AND (HedgeFile.[UnwindDate] <= 12/31/2018 AND HedgeFile.[Current Base Rate] = 0), '2. Terminated',
iif (HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
iif (HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK') AS FilterName
If anyone has any advice on how to tackle this, please advise. Looking forward to hearing from you!
sql ms-access switch-statement iif
asked Dec 28 '18 at 16:27
MannyManny
44
1
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20
|
show 1 more comment
Im getting a query error in MS Access "Expression too Complex in query expression". I have a bunch of nested IIF statements with complex Conditional Statements, i have about 23 of these total and currently im on 13 when it started breaking.
A common resolution i've seen is to create a seperate lookup table housing the values to be returned but i dont think this will work in my case since i have complex boolean logic.
I've researched the issue and i believe using SWITCH statement will cause the same issue.
Someone from another post suggested to break the IIF statement into 2 parts and then a 3rd final IIF to compare the two but havent given it a shot yet.
Expression Too Complex In Access 2007
iif( (HedgeFile.[UnwindDate] is Not Null OR HedgeFile.[UnwindDate] <> '') AND (HedgeFile.[UnwindDate] <= 12/31/2018 AND HedgeFile.[Current Base Rate] = 0), '2. Terminated',
iif (HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
iif (HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK') AS FilterName
If anyone has any advice on how to tackle this, please advise. Looking forward to hearing from you!
sql ms-access switch-statement iif
asked Dec 28 '18 at 16:27
MannyManny
44
Im getting a query error in MS Access "Expression too Complex in query expression". I have a bunch of nested IIF statements with complex Conditional Statements, i have about 23 of these total and currently im on 13 when it started breaking.
A common resolution i've seen is to create a seperate lookup table housing the values to be returned but i dont think this will work in my case since i have complex boolean logic.
I've researched the issue and i believe using SWITCH statement will cause the same issue.
Someone from another post suggested to break the IIF statement into 2 parts and then a 3rd final IIF to compare the two but havent given it a shot yet.
Expression Too Complex In Access 2007
iif( (HedgeFile.[UnwindDate] is Not Null OR HedgeFile.[UnwindDate] <> '') AND (HedgeFile.[UnwindDate] <= 12/31/2018 AND HedgeFile.[Current Base Rate] = 0), '2. Terminated',
iif (HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
iif (HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK') AS FilterName
If anyone has any advice on how to tackle this, please advise. Looking forward to hearing from you!
sql ms-access switch-statement iif
sql ms-access switch-statement iif
asked Dec 28 '18 at 16:27
MannyManny
44
asked Dec 28 '18 at 16:27
MannyManny
44
edited Dec 28 '18 at 17:56
Manny
asked Dec 28 '18 at 16:27
MannyManny
44
asked Dec 28 '18 at 16:27
MannyManny
44
asked Dec 28 '18 at 16:27
MannyManny
44
44
1
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20
|
show 1 more comment
1
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20
1
1
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20
|
show 1 more comment
1 Answer
1
active
oldest
votes
Improper parens.
If UnwindDate is a date/time field, need delimiters for the date parameter. If it is text type then need apostrophe delimiters. If it is a date/time type then field cannot hold a string so checking for string is not needed and might cause data type mismatch error. If field is text type then handle possible null or empty string with: HedgeFile.UnwindDate & "" <> "" (I never allow empty string in tables).
IIf(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
IIf(HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
IIf(HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK'))) AS FilterName
or
Switch(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
HedgeFile.[Code] = 'WSP', '4. Company Swaps', TRUE, 'OK') AS FilterName
answered Dec 28 '18 at 20:32
June7June7
4,22451126
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53961476%2fexpression-too-complex-error-while-using-complicated-iif-condition-statements-a%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Improper parens.
If UnwindDate is a date/time field, need delimiters for the date parameter. If it is text type then need apostrophe delimiters. If it is a date/time type then field cannot hold a string so checking for string is not needed and might cause data type mismatch error. If field is text type then handle possible null or empty string with: HedgeFile.UnwindDate & "" <> "" (I never allow empty string in tables).
IIf(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
IIf(HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
IIf(HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK'))) AS FilterName
or
Switch(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
HedgeFile.[Code] = 'WSP', '4. Company Swaps', TRUE, 'OK') AS FilterName
answered Dec 28 '18 at 20:32
June7June7
4,22451126
add a comment |
Improper parens.
If UnwindDate is a date/time field, need delimiters for the date parameter. If it is text type then need apostrophe delimiters. If it is a date/time type then field cannot hold a string so checking for string is not needed and might cause data type mismatch error. If field is text type then handle possible null or empty string with: HedgeFile.UnwindDate & "" <> "" (I never allow empty string in tables).
IIf(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
IIf(HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
IIf(HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK'))) AS FilterName
or
Switch(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
HedgeFile.[Code] = 'WSP', '4. Company Swaps', TRUE, 'OK') AS FilterName
answered Dec 28 '18 at 20:32
June7June7
4,22451126
add a comment |
Improper parens.
If UnwindDate is a date/time field, need delimiters for the date parameter. If it is text type then need apostrophe delimiters. If it is a date/time type then field cannot hold a string so checking for string is not needed and might cause data type mismatch error. If field is text type then handle possible null or empty string with: HedgeFile.UnwindDate & "" <> "" (I never allow empty string in tables).
IIf(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
IIf(HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
IIf(HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK'))) AS FilterName
or
Switch(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
HedgeFile.[Code] = 'WSP', '4. Company Swaps', TRUE, 'OK') AS FilterName
answered Dec 28 '18 at 20:32
June7June7
4,22451126
Improper parens.
If UnwindDate is a date/time field, need delimiters for the date parameter. If it is text type then need apostrophe delimiters. If it is a date/time type then field cannot hold a string so checking for string is not needed and might cause data type mismatch error. If field is text type then handle possible null or empty string with: HedgeFile.UnwindDate & "" <> "" (I never allow empty string in tables).
IIf(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
IIf(HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
IIf(HedgeFile.[Code] = 'WSP', '4. Company Swaps', 'OK'))) AS FilterName
or
Switch(Not HedgeFile.[UnwindDate] Is Null AND HedgeFile.[UnwindDate] <= #12/31/2018# AND HedgeFile.[Current Base Rate] = 0, '2. Terminated',
HedgeFile.[Port] IN ('ASSUME', 'HDLTV'), '3. Port Excluded from Model',
HedgeFile.[Code] = 'WSP', '4. Company Swaps', TRUE, 'OK') AS FilterName
answered Dec 28 '18 at 20:32
June7June7
4,22451126
answered Dec 28 '18 at 20:32
June7June7
4,22451126
answered Dec 28 '18 at 20:32
June7June7
4,22451126
answered Dec 28 '18 at 20:32
June7June7
4,22451126
4,22451126
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53961476%2fexpression-too-complex-error-while-using-complicated-iif-condition-statements-a%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Unless you need to run this query outside of Access, I would create a VBA function for that. Feed it all input parameters (UnwindDate, Port, Code, ...), and it returns the value. Additional advantage: you can write readable and maintainable code.
– Andre
Dec 28 '18 at 16:58
I would go the VBA route but unfortunately, this code will be converted to Oracle SQL once complete and working. Thanks for the pointer.
– Manny
Dec 28 '18 at 17:02
_ code will be converted to Oracle SQL_. Then you will have to convert it anyway. As @Andre suggests, use VBA, it won't fail.
– Gustav
Dec 28 '18 at 17:06
Converting it wont be my responsibility, i will be doing a hand off and it has to be in SQL format.
– Manny
Dec 28 '18 at 17:12
Then you are left with Switch - which you believe will cause the same issue.
– Gustav
Dec 28 '18 at 17:20