Select only non null fields

[Niall]

Does Access 97 have a command to create a query that will
select only non null fileds from one record?

 

[John Vinson]

Does Access 97 have a command to create a query that will
select only non null fileds from one record?

If you mean a query that will return five fields from one record,
eight from the next, and six from the third... no. All records in a
Query must have the same "shape".

Could you explain a bit more what you want, perhaps with an example of
the data and what you want to see?

 

[Guest]

Hi John

Basically i have a query which will only ever select one
row. Within this row some fields will have been left blank
(i.e. null). I want to be able to put this query into a
report and ignore all those fields that are null.

For example the table may have Four columns Key-Number
Name, Address and Tel if a user only inputs the name and
adress the query currently selects the one row by the Key
but i want it to ignore the Tel field as it is Null

 

[John Vinson]

Hi John

Basically i have a query which will only ever select one
row. Within this row some fields will have been left blank
(i.e. null). I want to be able to put this query into a
report and ignore all those fields that are null.

For example the table may have Four columns Key-Number
Name, Address and Tel if a user only inputs the name and
adress the query currently selects the one row by the Key
but i want it to ignore the Tel field as it is Null

Could you explain what you mean by "ignore"?

You can set the Report Textbox's CanShrink property to True. It will
vanish if the data in the field is NULL (with some limitations, see
the online help for CanShrink).

 

[Guest]

Thanks, I have tried the Can Shrink option and this works
for the text box but if the text box contains a null value
then I also want the associated label box not to print.

 

[John Vinson]

Thanks, I have tried the Can Shrink option and this works
for the text box but if the text box contains a null value
then I also want the associated label box not to print.

A couple of ideas come to mind...

1. Use a calculated field in the Query, or in the control source of
the textbox, concatenating your desired label with the field contents,
using the + operator to concatenate NULLS: e.g.

= "Optional Label" + [fieldname]

as the control source;

2. Toggle the Visible property of the label control in the Format
event of the report section.

 

[Guest]

still no luck unfortunatley however i have been trying the
following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
On Error GoTo err_Detail_Format

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If ctl.Text = "" Or IsNull(ctl.Text) Then
With ctl
..Visible = False
End With
Else
With ctl
..Visible = True
End With
End If
End If
Next ctl


exit_Detail_Format:
Exit Sub

err_Detail_Format:
MsgBox Err.Description
Resume exit_Detail_Format

End Sub

which does work to some effect but some times a get an
error sode which comes up saying that access can not
retieve the value of the property however i do not know
what property it is trying to find, any suggestions?

-----Original Message-----

Thanks, I have tried the Can Shrink option and this works
for the text box but if the text box contains a null value
then I also want the associated label box not to print.

A couple of ideas come to mind...

1. Use a calculated field in the Query, or in the control source of
the textbox, concatenating your desired label with the field contents,
using the + operator to concatenate NULLS: e.g.

= "Optional Label" + [fieldname]

as the control source;

2. Toggle the Visible property of the label control in the Format
event of the report section.


.

 

[John Spencer (MVP)]

Use the VALUE property of the control. The TEXT property only "exists" when the
control is the current control (has the focus)

....
If Len(Trim(Ctl.Value & vbNullString)) = 0 Then
Ctl.visible = false
End If

Of course, you could be a bit more esoteric with this

For each ctl in Me.Controls
If TypeOf Ctl is Textbox then
Ctl.Visible = Len(Trim(Ctl.Value & vbNullString)) > 0
End if
Next Ctl

still no luck unfortunatley however i have been trying the
following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
On Error GoTo err_Detail_Format

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If ctl.Text = "" Or IsNull(ctl.Text) Then
With ctl
.Visible = False
End With
Else
With ctl
.Visible = True
End With
End If
End If
Next ctl

exit_Detail_Format:
Exit Sub

err_Detail_Format:
MsgBox Err.Description
Resume exit_Detail_Format

End Sub

which does work to some effect but some times a get an
error sode which comes up saying that access can not
retieve the value of the property however i do not know
what property it is trying to find, any suggestions?

-----Original Message-----

Thanks, I have tried the Can Shrink option and this works
for the text box but if the text box contains a null value
then I also want the associated label box not to print.

A couple of ideas come to mind...

1. Use a calculated field in the Query, or in the control source of
the textbox, concatenating your desired label with the field contents,
using the + operator to concatenate NULLS: e.g.

= "Optional Label" + [fieldname]

as the control source;

2. Toggle the Visible property of the label control in the Format
event of the report section.


.