help-----iif function

[Mayo123]

I have a field with value -1 ,o or null. I need to show those records which has value -1 as Yes, the following codes always gives me an error on the report:
=IIf([Graduated]="-1","Yes","")
Could you please give me some advice!
Thanks in advance!

 

[lespaul]

remove the "" from around -1 should work:

=IIf([Graduated]=-1,"Yes","")

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Mayo123]

I tried, but still got #error on the report.
Graduated field's datatype is text.
Thanks!

 

[lespaul]

if it's a string, your original formula should work. What error do you get on the report?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Mayo123]

I got the same #error

 

[formerTexan]

Mayo,

How about a slight change in tactics:

Place the following as the controlsource for the report's textbox. I am assuming that Graduated is the name of a field in the report's recordsource.

=ConvertBool([Graduated])

And then add the following to the report's code module. Adjust it to whatever strings you want passed in place of the integer values.

Private Function ConvertBool(i As Integer) As String
If i = 0 Then
ConvertBool = "yes"
Else
ConvertBool = "no"
End If
End Function

Cheers,
Bill

 

[Mayo123]

Still I got #error on the report.
Thanks anyway, Bill

 

[lespaul]

what's the record source of the report?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Mayo123]

The record source of the report comes from a table directly.
Graduated is one of the fields from the table.
I copied the following codes in the report module, but it wasn't called from control source of the graduated field(=ConvertBool([Graduated]))
Any more advice! Thanks!

 

[cmmrfrds]

make sure it is not null with NZ function and plug 0.
Use Val function to make it numeric.


=IIf(val(nz([Graduated],0))=-1,"Yes","No")

 

[raskew]

Hi-

It sounds as if this calls for a Boolean vs. text field.
The test for Graduated is either true (-1) or false (0).
There's no place for Null. Displaying a checkbox bound
to a boolean field, the value is either -1 (Checkbox
checked) or 0 (Checkbox not checked).

Consider adding a new boolean field and populate it using
cmmrfrds previous post. Once that is successful, delete
field Graduated from your table and rename the boolean
field Graduated.

Now you can use: Iif([Graduated], "Yes", "No") which is
the short version of Iif([Graduated] = True, "Yes", "No")

Bob

 

[formerTexan]

What happens when you set the control's controlsource to Graduated and open the report? Are you seeing values or #error? The suggested procedures should work fine if they are getting the expected values.

Cheers,
Bill

 

[THWatson]

Just a thought...Are you trying to put this IIf expression in a text box that is already bound to a field from the table? If so, make a new unbound text box and put the IIf expression in it.

Tom

 

[Mayo123]

Sorry to reply to you all. I just came back from the vocation.
I found a solution by using a query as report's controlsource instead of using a table directly, in the query's criteria, I used the iif() statement, and everything is working the way I expected.

Thank you all for your help. I really appreciate it.

Mayo