Replace nulls in report with placeholder string to indicate no data

[Girraffa]

Hello folks!
I am struggling my way though creating some reports. They were created by saving forms as reports since they are not grouped/aggregated like many reports are. An example recordsource is below. Most of the fields are short or long text fields and they are not always filled out by the user, which is fine as not all fields are always applicable. I have the controls set to "can grow" and "can shrink" since the amount of text in them varies widely. However due to this when the control is null that text box disappears and the report's formatting gets jumbled. When the report is opened I would like the null text boxes to display " --" to indicate that no data was entered there, rather than the viewer wondering whether something is missing. (I've tried doing this in the format property of the control and/or of the table field but have discovered that causes truncation of text over 255 characters for long text fields.) Through many searches and code attempts I have arrived at the solution of doing something in the report's "on load" event such that it checks if a control value is null and puts something there if it is, however I am having trouble doing this. I am thinking I should use a Nz () or IIF () function (?)

So far I'm thinking something like this but it doesn't work and I wouldn't know how to efficiently string together a statement for multiple controls:

Private Sub Report_Load()
Dim Nills As String
Nills = Nz(Me.txtPulseQual, " --")
End Sub

Not sure if I'm on the right track. Any help appreciated!!!

Recordsource example:
SELECT ExamTable.ExamDate, ExamTable.CauseforExam, ExamTable.[Animal/GroupAge], ExamTable.Weight, ExamTable.WeightUnits, ExamTable.BodyTemp, ExamTable.TempUnits, ExamTable.RespRate, ExamTable.HeartRate, ExamTable.PulseQuality, ExamTable.CapillaryRefillTime, ExamTable.VitalsNotes, ExamTable.HistoryCheck, ExamTable.AppearanceGait, ExamTable.HEENM, ExamTable.Samples, ExamTable.DiseaseSigns, ExamTable.AnimalIDFK, ExamTable.ExamAutoID, ExamTable.ImpressionDiagnosis, ExamTable.TreatmentPlan, ExamTable.ReevaluationPlan, AnimalTable.FacilityAnimalID, AnimalTable.Species, AnimalTable.CommonName, AnimalTable.Sex
FROM AnimalTable INNER JOIN ExamTable ON AnimalTable.AnimalAutoID = ExamTable.AnimalIDFK;

[Gicu]

Hi Girraffa,

Would something like this work (assumes your controls that are shrinking are text boxes):

Private Sub Report_Open(Cancel As Integer)Dim ctrl As Control


For Each ctrl In Me.Controls
    If ctrl.ControlType = acTextBox Then
       ctrl.Value = Nz(ctrl.Value, "---")
    End If
Next ctrl


Set ctrl = Nothing


End Sub

Cheers,
Vlad

[ranman256]

doesnt the fact that there is no data in the cell indicate there's no data?

[moke123]

I have the controls set to "can grow" and "can shrink"

What about setting "Can Shrink" to No?

[Girraffa]

Vlad,
I tried that but it gets stuck on
ctrl.Vaue = Nz(ctrl.Value, "---")
with error: 2424
"the expression you entered has a field, control, or property name that Microsoft Access can't find."
Any ideas?
thank you!

[Girraffa]

Yes the absence of data implies no data, but for display sake and end users who are not familiar with database inner-workings I would like to be explicit about that. If I set "can shrink" to no I will end up with big empty areas in the report. I suppose I could just make the controls disappear when the values are null but ideally I would be able to insert some placeholder text--this feels like something that should be possible

[June7]

Maybe going out on a limb but want to say that using VBA to set Value property of textbox in report won't work. Ever. At All. Period.

I avoid long text (memo) type field because of their quirks.

Does this work in ControlSource:

=IIf(IsNull([fieldname]), "--", [fieldname])

[Girraffa]

June,
It sure does!! Thank you! yes I am learning more and more about the quirks of the long text fields, oi. Good to know about VBA. My next idea was to make text boxes that only appeared when those fields were null and put what I wanted in those...this is much easier. Thanks again!

[June7]

Let me qualify my earlier statement. VBA cannot directly set Value property in report. However, the ControlSource can call a VBA UDF (user defined function).