IIF statement syntax

[IKinal]

Hi,

I am just starting out to add programming to my reports. I'm trying to make an IIF statement work, but it is giving me errors. Here is the statement that I have:
=IIf([State] is null, 1, 0 )
Access is giving me the standard invalid syntax error message, but I don't know where the problem is. Can anybody spot it for me?

Thanks!
I. Kinal

 

[Aubs010]

but it doesn't have to be is what I am saying...

Even if he didn't have the Text1 there, he would still be able to reference [state] because it's in the record source for the whole report, so having ONLY one TextBox:

Name: Text1
Control: =IIF([state] is null,1,0)

would (should) work perfectly (imho)

Aubs

 

[IKinal]

Problem found! Thanks to Aubs who took a look at my database and sent me a solution. The problem was the commas - replacing them with semicolons worked! And I had had this problem once before with another statement, but I just completely forgot about that. Thanks to everyone that helped out!

I. Kinal

 

[scriverb]

Don't understand why semi-colons would make a difference:
ACCESS HELP
Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

No mention of semicolons in ACCESS Help. If it gets it to work that's fine but I would not count on that all the time. Commas should work and do as by design.

Bob Scriver

 

[Aubs010]

I didn't put any semicolons in at all!!!!

in the record source for the report, I put:
SELECT Table1.[Job Number], Table1.state FROM Table1;

Job number was something I added to the table to show how a job number might have a state as[ol][li]Null[/li][li]Complete[/li][li]Non-Complete[/li][/ol] and to represent these in the report...


The report had the following TextBoxes:

Name: Text1
Control: Job Number

Name: Text2
Control: state

Name: Text3
Control: =IIf([state]="Complete",1,2)

Name: Text4
Control: state

Name: Text5
Control: =IIf([text4] Is Null,"Above is NULL","Above is not NULL&quot

There are obviously labels to show IKinal what each does...

I think he means the quotes/speechmarks (""'s)

Anyways, if all is working, I'm please for you IKinal happy designing!!

Aubs

 

[IKinal]

Ah, now this is interesting. I think that this is caused by the fact that I am using different regional settings. I took a look and found that my list separator is a ; , and I am guessing that that is why this is happening. When I opened the database that Aubs sent me, Access automatically converted his commas into semicolons based on my regional settings. So I guess this was all really my own fault - still a good learning experience though. Thanks again to everyone for their help.

I. Kinal

 

[Aubs010]

np IKinal, Always happy to help where I can - With my limited knowledge!!!

Aubs