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

 

[scriverb]

Try this:

=IIf(IsNull([State]), 1, 0 )

This is the IsNull function. This should work for you.


Bob Scriver

 

[IKinal]

Hi,

I copied and pasted your statement, but I still recieve the same syntax error?

I. Kinal

 

[scriverb]

Back to basics. There is a control named State in the report? Yes?

Bob Scriver

 

[IKinal]

Yes, and I also tried it with other fields - same thing.

I. Kinal

 

[scriverb]

Try this:

=IIf(IsNull(Me.State), 1, 0 )

Just trying other syntax that I know work. Now once again State is a control in the report right? Not just a field in the reports Record Source table or query.

Bob Scriver

 

[IKinal]

Hmm, could you please clarify the difference between a control and a field in the report's source query?

I. Kinal

 

[scriverb]

A report has a Record Source property which is either a Table or a Query. These are the records of a recordset that you want to fashion into a report. The fields in this recordset are the "FIELDS" that I was referring to.

In your Reports Sections(Details, Group Headers and Footers, Page Headers and Footers, and Report Headers and Footers are controls(i.e. text boxes that display the FIELDS from the Record Source defined above. These controls have a Control Source property that can be a direct link to one of the Record Sources FIELDS or an EXPRESSION to be displayed.

The IIF statement that you are trying to get to work must be referring to one of the reports controls. It cannot be referring to one of the FIELDS in the table. You must have a control(text box) called State in one of your sections for this statement to work. You are referring to a Report text box called State. You can't be referring to a FIELD directly.

I hope that this helps.

Bob Scriver

 

[Aubs010]

the easiest way to do this is to

[ol]
[li]create a new TextBox (unbound).[/li]
[li]Click on it once and press F4 to open the properties dialog.
[li]Click on the tab called Data[/li]
[li]In the Control Source, click on the drop down menu (right of the white box).[/li]
[ul]
[li]Select one of the items (you may see State - click on that)[/li]
[li]If is nothing in the drop down menu, then there are no control sources in the report.[/li]
[/ul]
[li]Save the report and open it in Print Preview.[/li]
[li]Where you placed the TextBox, should now show something (Not an error!!)[/li]
[li]you can now work with that text box, because you know [state] or what ever you chose is valid.[/li]
[/ol]


Aubs

 

[IKinal]

Ah, I see - thanks for the explanation. So I now created a text box and named it State with a control source of State. The expression is still giving me a syntax error (in a different text box, of course).

I. Kinal

 

[Aubs010]

If you are using iif as the control source and referencing the name of the text box within the iif, then you would get an error.

Try this TextBox:

Name: State_Item
Control Source: =iif([state] is null,1,0)

This way, you are not confusing access into thinking that the control is seing if itself is null, do something...

If you understand that...

Aubs

 

[CosmoKramer]

Aubs010 is right. Name your text box something like txtState. The control must be unbound. You can't use functions like Iif as the control source of a bound control.

 

[IKinal]

Sorry, I should have been more clear. I am putting the iif statment into a different text box, not the one that I named state with control source state. Not that I think it matters, but I just left the default Access name for the text box where I am trying to put the iif statement.

I. Kinal

 

[scriverb]

Yes, that is the problem. I recognized that you were trying to analyze another control named State with this IIF statement. It is okay to have the other control named State and have the Control Source be State. But, you had to have a control named State to have the IIF be effective.

That should now work for you.

Bob Scriver

 

[Aubs010]

ok, then if you have a TextBox called state, change that name.

So, for example in your report, have ONLY the following text boxes with their corresponding controls:


Name: Text1
Control: [state]

Name: Text2
Control: =IIF([state] is null,1,2)

That wouldn't bring a problem, however, this would (might):


Name: state
Control: state

Name: Text2
Control: =IIF([state] is null,1,2)



Aubs

 

[Aubs010]

Carrying on from my last post,

But this would definately cause a problem:

Name: State
Control: =IIF([state] is null,1,2)

Aubs

 

[IKinal]

Ok. I created a new report whose only recordsource is state from a table. I copied your scenario from above:
Name: Text1
Control: [state]

Name: Text2
Control: =IIF([state] is null,1,2)

The IIF statement is still giving me a syntax error!

I. Kinal

 

[CosmoKramer]

You have to use the Name of the control.....

 

[Aubs010]

Cosmos:

If the report already has [state] in the recordsource, then he would not have to reference the other TextBox (if that's what you mean)

I. Kinal:

I don't know what to suggest!

If you want, email the db to me. Aubs010ANTISPAM@yahoo.co.uk
(removing the ANTISPAM)

and I'll havea

Aubs

 

[CosmoKramer]

It should be:

Name: Text2
Control: =IIF([Text1] is null,1,2)