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-----