YesNoNull Function for reports

[Guest]

I have a number of triple-state checkboxes on my forms, linked to Long
Integer values. The possible values are, I guess, -1, 0, Null and perhaps
Empty.
I am trying to create a utility function that will return a string value of
"Yes", "No" or "", which I will print on my report. My best guess is this:

Function YesNoNull(ynn As Long) As String
If IsNull(ynn) Then
YesNoNull = ""
Else
YesNoNull = IIf(ynn, "Yes", "No")
End If
End Function
But it still gives #Error values whenever the checkbox is set to the null or
empty state. What am I doing wrong? Or is there a better way to display
Yes/No/blank for a triple-state checkbox field? Thanks!

 

[Ken Snell \(MVP\)]

When do you run this function? Are you calling it from the report?

Why not use a calculated field in the query that you use for the report's
Recordsource, and then bind a textbox on the report to that calculated
field:

SELECT *, (IIf(IsNull([FieldName] = True, "",
IIf([FieldName] = True, "Yes", "No")) AS YesNoNullText
FROM TableName;

Then bind a textbox to the YesNoNullText field.

 

[Rob Parker]

To accept a null as the input to the function, you need to declare the input
parameter as Variant, rather than long:

Public Function YesNoNull(ynn As Variant) As String
...

HTH,

Rob

 

[Guest]

You're right; it works. I had thought I couldn't do it all in one line like
that because the IIF statements "evaluate" the true and false parts,meaning
that (so I thought) it would evaluate
IIf([FieldName] = True)
and generate an error even if the value were null.
Live and learn.
Thanks!

When do you run this function? Are you calling it from the report?

Why not use a calculated field in the query that you use for the report's
Recordsource, and then bind a textbox on the report to that calculated
field:

SELECT *, (IIf(IsNull([FieldName] = True, "",
IIf([FieldName] = True, "Yes", "No")) AS YesNoNullText
FROM TableName;

Then bind a textbox to the YesNoNullText field.

--

Ken Snell
<MS ACCESS MVP>

I have a number of triple-state checkboxes on my forms, linked to Long
Integer values. The possible values are, I guess, -1, 0, Null and perhaps
Empty.
I am trying to create a utility function that will return a string value
of
"Yes", "No" or "", which I will print on my report. My best guess is
this:

Function YesNoNull(ynn As Long) As String
If IsNull(ynn) Then
YesNoNull = ""
Else
YesNoNull = IIf(ynn, "Yes", "No")
End If
End Function
But it still gives #Error values whenever the checkbox is set to the null
or
empty state. What am I doing wrong? Or is there a better way to display
Yes/No/blank for a triple-state checkbox field? Thanks!

 

[Ken Snell \(MVP\)]

If you use an IIf function in VBA code, then you are right -- VBA will
evaluate both the true and false arguments of an IIf statement before it
does the test to see which one is to be used -- and that can lead to errors
when the VBA runs if one argument results in an error.

However, Jet does not evaluate an argument expression until it does the
test, and then it evaluates only the argument that matches the test results;
therefore, you can safely use the IIf in this way in an SQL statement
(query). The same result occurs if you use an IIf function in a
ControlSource expression of a control on a form or report -- ACCESS
evaluates only the argument that matches the test result.

--

Ken Snell
<MS ACCESS MVP>

You're right; it works. I had thought I couldn't do it all in one line
like
that because the IIF statements "evaluate" the true and false
parts,meaning
that (so I thought) it would evaluate
IIf([FieldName] = True)
and generate an error even if the value were null.
Live and learn.
Thanks!

When do you run this function? Are you calling it from the report?

Why not use a calculated field in the query that you use for the report's
Recordsource, and then bind a textbox on the report to that calculated
field:

SELECT *, (IIf(IsNull([FieldName] = True, "",
IIf([FieldName] = True, "Yes", "No")) AS YesNoNullText
FROM TableName;

Then bind a textbox to the YesNoNullText field.

--

Ken Snell
<MS ACCESS MVP>

I have a number of triple-state checkboxes on my forms, linked to Long
Integer values. The possible values are, I guess, -1, 0, Null and
perhaps
Empty.
I am trying to create a utility function that will return a string
value
of
"Yes", "No" or "", which I will print on my report. My best guess is
this:

Function YesNoNull(ynn As Long) As String
If IsNull(ynn) Then
YesNoNull = ""
Else
YesNoNull = IIf(ynn, "Yes", "No")
End If
End Function
But it still gives #Error values whenever the checkbox is set to the
null
or
empty state. What am I doing wrong? Or is there a better way to
display
Yes/No/blank for a triple-state checkbox field? Thanks!