473,322 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Complex "IIf" Statement erro

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
3 4840
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Susie Swint | last post by:
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...
2
by: k7i5t3n | last post by:
I have two tables I'm using in a query. I have pulled several fields from 'tblEmployeeList' and need to pull a Yes/No field 'FileStampNumber' from the table 'tblFileStamps'. However, I need to...
4
by: Jim Florence | last post by:
Hello, I've just started in ASP and I'm having a few teething problems. Initially I tried to write out dates from the database using <asp:Label runat="server" ID="Label6" Text='<%#...
1
by: Vishnu Kumar Tiwari | last post by:
I am using below expression in xsd... I am getting devide by zero error. For ur information I am filling the dataset with the results obtained from Stored procedure. Now again I need to...
4
by: mukesh | last post by:
One another problem I have used a formula in the calculated field of a query as - IIf( <=, (IIf(<=, , ), (IIf (<=, , )) the above formula is working properly to give the lowest of , and ...
2
by: bhdvir | last post by:
Does anybody have any advice on selecting all records within a iif-statement? I have a form in which one has to state if all projects or just a selection of projects are to be used for a query....
4
by: ebasshead | last post by:
Hi Everybody, I want to be able to put a number in a txt box and if its between 5 and 9, for it to appear as a count in another txt box ie if the number inputed is 7 then 1 will appear in the second...
1
by: Chesne | last post by:
I am trying to return several value in and IF in a query statement. The generic is as follows... =IIf(!!=10,Between 1 and 10,"") Can anyone help with the "Between" part. I realise that it...
2
by: Echidna | last post by:
Hi Guys, I am a bit of a newbie in these things, and need a little guidance to nudge me in the right direction. I am having a bit of a problem getting my head around this one, I am attempting...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.