problem with IIF

[Tom123456]

Hello all, please help me with this one. Whats wrong with this IIF.
(Form!F_Order.payment is a checkbox, [sum] is av value from database)

=IIF((Form!F_Order.payment),([sum]/3),4000)


This is placed in the controlsource of a textfield.

- Tom

 

[Trendsetter]

The only thing I can see wrong is:
Form!F_Order.payment
should be:
Form!F_Order!payment

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Tom123456]

Is still says "wrong syntax", could it be that it´s in a report?

- Tom

 

[Tom123456]

I even get a error message with this one.
=iif(3<5,2,3)

&quot;The expression contains a non number&quot; (my translation)

- Tom

 

[Trendsetter]

I've just played with a similar thing and have used
=IIf([Check33]),&quot;Yes&quot;,&quot;No&quot
and that worked, when I tried Me.Check33 it didn't but [Forms]![frmOptions]![Check33] did.

Anyway I think the expression should be:
Hello all, please help me with this one. Whats wrong with this IIF.
(Form!F_Order.payment is a checkbox, [sum] is av value from database)

=IIF(([Form]![F_Order]![payment]),([sum]/3),&quot;4000&quot
I have the sample if you want to see it!


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Tom123456]

I get a error message like this.

&quot;syntaxerror in queryexpression 'First([IIF(([Forms]]![F_Order]![[payment]),[[sum]/3),&quot;4000&quot])'&quot;

What means with &quot;First&quot; and all these &quot;[&quot; and &quot;]&quot;, excause me for my lack of experience with access but I´m learning everyday





- Tom

 

[danvlas]

Tom and Frank:
Both
Form!F_Order.payment
and
Form!F_Order!payment
are wrong.

Either
Forms!F_Order.payment
or
Forms!F_Order!payment
should work

Try:
=IIF(Forms!F_Order.payment = True, Nz([sum],0)/3, 4000)

The green parts are there just to cover some 'unexpected' values, such as Null. It should work without them as well.

You say [sum] is a value from the database. The value should be somehow 'visible' to the form - in the record source or in a control on the form.

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Tom123456]

Doesn't work either. Is my problem that i´m using a textfield in a report?

- Tom

 

[Tom123456]

Access doesn´t like the &quot;=&quot; in the begining of the string. And if I remove it Access generates a error like the one above (with the 'First...)

- Tom

 

[danvlas]

Does any IIf statement work?
Try to compile the project: open any module and go to Debug-Compile.
If no error occurs, type this i the Immediate Window:
?IIf(&quot;a&quot;=&quot;a&quot;,&quot;match&quot;,&quot;no match&quot
It should return 'match'

Then try
?IIf(&quot;a&quot;=&quot;b&quot;,&quot;match&quot;,&quot;no match&quot
It should return 'no match'


See what happens and post back

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Tom123456]

can´t I do it in the controlsource of the textfield?

- Tom

 

[danvlas]

Yes, but change ? to =

You have just said it didn't like the equal sign ...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Tom123456]

Thanks for you help, now I got the IIF to work:

Private Sub Report_Open(Cancel As Integer)
Dim test
test = IIf(checkbox, &quot;match&quot;, &quot;no match&quot

MsgBox test
End Sub

(returns match if checked)

But now a second problem arise. I can´t give the textfield in the report a value.




- Tom

 

[danvlas]

I'm not sure I understand what you are after...

Use an unbound text box and the Format event for the corresponding section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
YourTextBox = IIf(checkbox, &quot;match&quot;, &quot;no match&quot
End Sub

If this is not what you want, post more details, maybe a description of the process...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html