excluding fields with null values (1 Viewer)

[jeeper987]

I want to create a report from a query. Many of the records have null values. I would like for the report to exclude the field name and the space where the value would be. In other words, I would like for it to look like there is not a field there. I need this in order to make the report look more organized and easier to read. Please help!!!

 

[Sohaila Taravati]

Go to the query behind your report and put :
Is Not Null in your field that you are talking about. That should get rid of the null values

 

[jeeper987]

If I put "is not null" in the fields, will it not exclude the entire record. I just want to exclude one field out of a record. The report will have name, address, phone, fax, email as the fields. If the person does not have fax number, I do not want the report to put the caption "fax" or a blank space for the null value. The report would then only have name, address, phone, email. I hope I am making sense. thanks

 

[Rich]

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.MyField.Visible=Not IsNull(Me.MyField)
End Sub

 

[jeeper987]

Works great Rich, Now, is there a way I can apply this to all fields in the report, without having to do it for each field individually.

Thanks

 

[jeeper987]

also, is there a way to delete the empty space where the field would have been or to move the next visible field up into its place so that there will not be any gaps in the report.

 

[Pat Hartman]

If you set the CanShrink/CanGrow properties of the detail section and all of its controls to yes, Access will reclaim vertical space if an entire line is blank. There is no similar method (for obvious reasons) of reclaiming horizontal space.

 

[kbrooks]

This is exactly what I was searching for, so I'm resurrecting this thread. Where exactly do I put that code in? I don't see anywhere in the properties of the individual fields that really made sense. I tried in the On Open event of the form detail but I got an error.

 

[Rich]

Form or Report?

 

[kbrooks]

Sorry, report.

 

[Pat Hartman]

Look in the properties dialog. Each control and Each section have a can grow/can shrink property. Make sure you have no overlapping controls or the section won't shrink. If you want to hide the label (and it is in the same section as the data field), put code into the format event for the detail section.

If IsNull(me.somefield) then
me.somefield.visible = false
else
me.somefield.visible = true
End if

 

[gemma-the-husky]

but surely

if you have a report (effectively a continuous form, printed) each row stands on its own

if you have blanks in a row, you surely cant expect or want to move up an item from the following row

so if there are, in a given row, some fields with values, and some blanks you either want to print the row or not

if you do, then fine, and you can manipulate the blank cells to some extent

but if you dont want to print a row, where any (important) part is blank, then surely the best way to dfo this is to exclude the row in the reports underlying query.

 

[Pat Hartman]

Gemma, the problem arises with multi-row sections. Labels are an excellent example:

Name
Addr1
Addr2
City, State, Zip

For the addresses that have Addr2, you want to show it but for those who don't, you want the empty row removed and the City, State, Zip line moved up. If you don,t remove the extra space, the three-line address' look funny.

 

[jehovahzapple7]

I am trying to create a report of the same but I don't understand where to put the following things:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.MyField.Visible=Not IsNull(Me.MyField)
End Sub

If IsNull(me.somefield) then
me.somefield.visible = false
else
me.somefield.visible = true
End if

Could you please help me?

 

[jehovahzapple7]

Empty Fields

I have created a table with info for chart audit reviews..
It includes fields that are sometimes empty. We need a report that includes all information but will not print the empty fields. I don't know how to do this and I'm not very good at any of this yet. Could you please help me?
​