null values in reports

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

How do you get zeros to display on a report when the
query result is a null value? I've tried the Nz function
and it doesn't display anything.
 
PMFBI

Won't "+" propagate the Null,
so every returned field will be 0?

There may be better, but what I use:

IIF(Len(Trim([myfield] & ""))=0,0,[myfield])

or (maybe overkill), wrap in function

Public Function IsNullZLSEmpty(varTest as Variant) As Boolean

If Len(Trim(varTest & "")) = 0 Then
IsNullZLSEmpty = True
Else
IsNulZLSEmpty = False
End If

End Function

so, in query:

IIF(IsNullZLSEmpty([myfield]),0,[myfield])

Again, apologies for butting in, especially
if I am wrong.

Gary Walter

Amy,

The value your looking at might not be null; it might be empty. Try using
this:
Nz([myfield] + null, 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Amy said:
How do you get zeros to display on a report when the
query result is a null value? I've tried the Nz function
and it doesn't display anything.
Click to expand...
Click to expand...
 
Gary:
You're absolutely correct.

Amy:
Sorry, I didn't test it properly. I should have!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Gary Walter said:
PMFBI

Won't "+" propagate the Null,
so every returned field will be 0?

There may be better, but what I use:

IIF(Len(Trim([myfield] & ""))=0,0,[myfield])

or (maybe overkill), wrap in function

Public Function IsNullZLSEmpty(varTest as Variant) As Boolean

If Len(Trim(varTest & "")) = 0 Then
IsNullZLSEmpty = True
Else
IsNulZLSEmpty = False
End If

End Function

so, in query:

IIF(IsNullZLSEmpty([myfield]),0,[myfield])

Again, apologies for butting in, especially
if I am wrong.

Gary Walter

Amy,

The value your looking at might not be null; it might be empty. Try using
this:
Nz([myfield] + null, 0)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Amy said:
How do you get zeros to display on a report when the
query result is a null value? I've tried the Nz function
and it doesn't display anything.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top