I have an Access 2003 report and some of the fields within the report are NULL. I can use conditional formatting to change the color and other aspects of the text-box control but what I want to do is to display "XXXXXXXXXXXXXX" in fields where there is no data (i.e. NULL). Can this be done using conditional formatting?
A:
I don't see how to do what you're asking for. So, instead I'll suggest using a query as the report's record source and using the Nz() function to transform Null values.
SELECT Nz(YourField,"XXXXXXXXXXXXXX") AS null_replaced
FROM YourTable;
HansUp
2010-10-18 20:46:46
+1
A:
The best place to do this is in the query which the report should be based on. However, it is possible to set a control in a report to, say:
=Nz(MyField,"Text")
You will have to ensure that the control has a name other than the default assigned name, which is the name of the field.
Remou
2010-10-18 20:47:17
Why should it be in the underlying recordsource? This is a presentation-layer issue, and to me, it absolutely belongs only in the controls and *not* in the underlying query/recordsource.
David-W-Fenton
2010-10-19 01:21:48
The OP wants to substitute a text string for Null values. To me, that is a calculated field expression in a query.
HansUp
2010-10-19 04:16:27
But he wants to do it in a report. Nothing has been said about, say, sorting on the result, or doing anything else with it. It seems clearly to be something that does not need to be done in the underlying recordset, but can be done just fine in the controls on the report.
David-W-Fenton
2010-10-19 23:04:44
It can be done just fine in the control or with a field expression in the recordsource query. I disagreed with "absolutely belongs only in the controls and not in the underlying query/recordsource". For me, anything that can be accomplished simply and effectively in the query will happen there.
HansUp
2010-10-20 13:38:37
But if you put it in the presentation layer, you guarantee that it will only be calculated when it's displayed. If you put it in the underlying query it may or may not get calculated even if it's never displayed anywhere. To me, that is a clear rule: if it's for display only, calculate it in the control.
David-W-Fenton
2010-10-21 04:49:56
HansUp
2010-10-21 05:19:05