No Data for Control on Report

[mswilson16]

Hi guys,

I have a reasonably large MDB. I have numerous reports that run fromt his database. 6 of the reports use a number of tables and queries for the record source. This is making the report slow, but it runs fine at the minute.

What I need to do is to make the report fill in blank spaces with "N/A". Because the report is so slow and a load more if statements would slow it down even more I was going to do this with something like

******************
Dim ctl As Control

For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "txt" Then
If IsNull(ctl.Value) = True Then
ctl.Text = "N/A"
End If
End If
Next
******************

But the problem that I am having is that I can not change the value/text/caption or anything of the textbox once the report is in preview view.

Does anyone know what property I should be trying to change?

Thanks

Mswilson

 

[GingerR]

i tried lots of things, but looks like you can set the value of a control either OnOpen, OnFormat or OnPrint. So I did this instead:

Make a new module and paste this into it:

Function NA(ReportValue)
    If IsNull(ReportValue) Or ReportValue = "" Then
        NA = "N/A"
    Else
        NA = ReportValue
    End If


End Function

then in a text box on your report, instead of the ControlSource being the field, call the function instead (tweak of course to use your field names in lieu of 'data'):

=NA([Data])

this will put in an "N/A" if it's empty, but put in the value of the data if it's not empty.

g

 

[mswilson16]

that works great... the only thing that concerns me is the performance of the report. I have around 40-50 controls on the reports and if I make all of the call this function... will that not grind the MDB to a halt?

 

[mswilson16]

also I it doesn't work with dates... how should I adjust the function to cope with dates?/??

thanks GingerR

 

[GingerR]

i can't imagine it 'grinding to a halt'. it may be slower, i dunno...try it out. make a copy of the report and leave just 10 controls; make a copy of THAT. tweak copy #2 with the new code; run both and see how much longer #2 takes than #1.

the only other thing i can think of is changing the underlying query for each field, i.e.

iif(Blah is null,"N/A",Blah)

and i don't know if that will take more time either....let me know how it goes tho...

 

[mswilson16]

It doesn't really slow it down at all. I had created a second report and put it on every control... the only thing that it had trouble with was date fields. but it wasn't slow at all.

 

[dhookom]

If you want to show "N/A" in a bound control then you only have to set the Format property to something like this expression from Access Help:
$#,##0.00[Green];($#,##0.00)[Red];"Zero";"N/A"


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]