By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Complex "IIf" Statement erro

P: n/a
I have the following IIf statement which worked in Access 95 but will
not work in Access 2002. The error message I get is that the expression
is typed incorrectly, or is too complex to be evaluated. Can you tell
me what is wrong with the statement?

Audits: IIf([Scorecard # Audits].[Qtr1] Is Not Null,[Scorecard #
Audits].[Qtr1],IIf([Scorecard # Audits].[Qtr2] Is Not Null,[Scorecard #
Audits].[Qtr2],IIf[Scorecard # Audits].[Qtr3] Is Not Null,[Scorecard #
Audits].[Qtr3],IIf([Scorecard # Audits].[Qtr4] Is Not Null,[Scorecard #
Audits].[Qtr4],0))))

Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Susie Swint wrote:
I have the following IIf statement which worked in Access 95 but will
not work in Access 2002. The error message I get is that the expression
is typed incorrectly, or is too complex to be evaluated. Can you tell
me what is wrong with the statement?

Audits: IIf([Scorecard # Audits].[Qtr1] Is Not Null,[Scorecard #
Audits].[Qtr1],IIf([Scorecard # Audits].[Qtr2] Is Not Null,[Scorecard #
Audits].[Qtr2],IIf[Scorecard # Audits].[Qtr3] Is Not Null,[Scorecard #
Audits].[Qtr3],IIf([Scorecard # Audits].[Qtr4] Is Not Null,[Scorecard #
Audits].[Qtr4],0))))


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You've forgotten the open parenthesis after an IIf in the 3rd line.
This is made apparent by the character shift to the left in the 3rd
line. Lines 2, 3 & 4 should all be the same length since they contain,
essentially, the same words. Line 3 is 1 character short. Below is the
fixed statement.

Audits: IIf([Scorecard # Audits].[Qtr1] Is Not Null,[Scorecard #
Audits].[Qtr1],IIf([Scorecard # Audits].[Qtr2] Is Not Null,[Scorecard #
Audits].[Qtr2],IIf([Scorecard # Audits].[Qtr3] Is Not Null,[Scorecard #
Audits].[Qtr3],IIf([Scorecard # Audits].[Qtr4] Is Not Null,[Scorecard #
Audits].[Qtr4],0))))

===

This looks like a prospect for Normalization. Instead of 4 columns: 1
column for the quarter w/ a number denoting which quarter; another
column for the value that was placed in each of your qtr columns. This
would make the query easier:

SELECT QtrValue
FROM TableName
WHERE Qtr Is Not Null

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQH2ZJIechKqOuFEgEQJkgQCgjvGnxZZglCdZ8W3oElNlTh p40ZwAoJ2i
XSFdGoLdc6SfIKMJO57KUgJf
=HfyG
-----END PGP SIGNATURE-----

Nov 12 '05 #2

P: n/a
Susie Swint wrote:
I have the following IIf statement which worked in Access 95 but will
not work in Access 2002. The error message I get is that the expression
is typed incorrectly, or is too complex to be evaluated. Can you tell
me what is wrong with the statement?

Audits: IIf([Scorecard # Audits].[Qtr1] Is Not Null,[Scorecard #
Audits].[Qtr1],IIf([Scorecard # Audits].[Qtr2] Is Not Null,[Scorecard #
Audits].[Qtr2],IIf[Scorecard # Audits].[Qtr3] Is Not Null,[Scorecard #
Audits].[Qtr3],IIf([Scorecard # Audits].[Qtr4] Is Not Null,[Scorecard #
Audits].[Qtr4],0))))

Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


See if changing the Is Not Null's to Not IsNull works

Audits : IIf(Not IsNull([Scorecard # Audits].[Qtr1])...

Nov 12 '05 #3

P: n/a
Field names should not have special characters. # is a special character.

Is Not Null should be written as Not Is Null. Better use the IsNull function.
Not IsNull([YourFieldname]).

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Susie Swint" <su*********@delta.com> wrote in message
news:40*********************@news.frii.net...
I have the following IIf statement which worked in Access 95 but will
not work in Access 2002. The error message I get is that the expression
is typed incorrectly, or is too complex to be evaluated. Can you tell
me what is wrong with the statement?

Audits: IIf([Scorecard # Audits].[Qtr1] Is Not Null,[Scorecard #
Audits].[Qtr1],IIf([Scorecard # Audits].[Qtr2] Is Not Null,[Scorecard #
Audits].[Qtr2],IIf[Scorecard # Audits].[Qtr3] Is Not Null,[Scorecard #
Audits].[Qtr3],IIf([Scorecard # Audits].[Qtr4] Is Not Null,[Scorecard #
Audits].[Qtr4],0))))

Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.