Hide Blank fields in query results

  • Thread starter Thread starter Patti
  • Start date Start date
P

Patti

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti
 
Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom Ellison said:
Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP

Ellison Enterprises - Your One Stop IT Experts


I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti
Click to expand...
Click to expand...

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
 
Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom Ellison said:
Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP

Ellison Enterprises - Your One Stop IT Experts


I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti
Click to expand...
Click to expand...

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
 
Tom Ellison said:
Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom Ellison said:
Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP

Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti
Click to expand...

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...

Tom

Thank you. I will give this a try and let you know how this works.
Again thank you for your help.

Patti
 
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom Ellison said:
Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP

Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti
Click to expand...

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
 
Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti


Tom Ellison said:
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
Patti,

Since Tom hasn't yet answered, let me at least clear up some of what I see.

Haven't fully reviewed this thread, but you do have a syntax error in your SQL.
You have an extra comma before As Label in each of your queries. Also, don't
know if you are planning to continue on adding queries to the union or not. If
not, the last UNION ALL needs to be dropped

SELECT Estimates2.[Job#], 1 AS Seg,"Job#" AS Label, [Job#] AS Value
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study" AS Label , [Study]
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms" AS Label, [Bdrms]
FROM Estimates2
WHERE [Bdrms] IS NOT NULL


Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti

Tom Ellison said:
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 30 Dec 2003 03:18:26 -0800, (e-mail address removed) (Patti) wrote:

Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
Dear Patti:

Please try this instead:

SELECT [Key], 1 AS Seg, "Job#" AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT [Key], 2 AS Seg, "Study" AS Label , [Study] AS Value
FROM Estimates2
WHERE [Study] IS NOT NULL
UNION ALL
SELECT [Key], 3 AS Seg, "Bdrms" AS Label, [Bdrms] AS Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL

The first column above I have changed to read [Key]. This needs to be
a the same column in all 3 parts above. This column (or more than one
column) should be the unique key to this data, something that uniquely
identifies each row. I cannot tell what that would be.

It will be used later to order the results. The results will be
sorted by this [Key], then by Seq (I didn't mean Seg but rather Seq
for "sequence") The way I understand it, when you report the data,
you will need to keep each set of [Job#], [Study], and [Bdrms]
together according to this [Key]. If there is something that labels
each set of [Job#], [Study], and [Bdrms] on the report then that is
probably the [Key] I'm looking for here.

From looking at what you have shown here, I wonder if this [Key] may
be [Job#]. Is that the unique thing separating each set from each
other set?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti


Tom Ellison said:
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 30 Dec 2003 03:18:26 -0800, (e-mail address removed) (Patti) wrote:

Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
Tom

Job# is the ONLY unique key in the table, it is alos the only field
that will have to have something in it. Being this is so do I have to
change the SQL statement? ( i am new to sql and vb ) thanks for your
help

Patti







Tom Ellison said:
Dear Patti:

Please try this instead:

SELECT [Key], 1 AS Seg, "Job#" AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT [Key], 2 AS Seg, "Study" AS Label , [Study] AS Value
FROM Estimates2
WHERE [Study] IS NOT NULL
UNION ALL
SELECT [Key], 3 AS Seg, "Bdrms" AS Label, [Bdrms] AS Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL

The first column above I have changed to read [Key]. This needs to be
a the same column in all 3 parts above. This column (or more than one
column) should be the unique key to this data, something that uniquely
identifies each row. I cannot tell what that would be.

It will be used later to order the results. The results will be
sorted by this [Key], then by Seq (I didn't mean Seg but rather Seq
for "sequence") The way I understand it, when you report the data,
you will need to keep each set of [Job#], [Study], and [Bdrms]
together according to this [Key]. If there is something that labels
each set of [Job#], [Study], and [Bdrms] on the report then that is
probably the [Key] I'm looking for here.

From looking at what you have shown here, I wonder if this [Key] may
be [Job#]. Is that the unique thing separating each set from each
other set?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti


Tom Ellison said:
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 30 Dec 2003 03:18:26 -0800, (e-mail address removed) (Patti) wrote:

Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
John

Thanks for your input. I will try this as well.

Patti





John Spencer (MVP) said:
Patti,

Since Tom hasn't yet answered, let me at least clear up some of what I see.

Haven't fully reviewed this thread, but you do have a syntax error in your SQL.
You have an extra comma before As Label in each of your queries. Also, don't
know if you are planning to continue on adding queries to the union or not. If
not, the last UNION ALL needs to be dropped

SELECT Estimates2.[Job#], 1 AS Seg,"Job#" AS Label, [Job#] AS Value
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study" AS Label , [Study]
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms" AS Label, [Bdrms]
FROM Estimates2
WHERE [Bdrms] IS NOT NULL


Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti

Tom Ellison said:
Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 30 Dec 2003 03:18:26 -0800, (e-mail address removed) (Patti) wrote:

Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
Dear Patti:

I think I know enough now to put something together for you. Your
report could be based on this:

SELECT [Job#], 1 AS Seg, "Job#" AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT [Job#], 2 AS Seg, "Study" AS Label , [Study] AS Value
FROM Estimates2
WHERE [Study] IS NOT NULL
UNION ALL
SELECT [Job#], 3 AS Seg, "Bdrms" AS Label, [Bdrms] AS Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
ORDER BY [Job#], Seg

The ORDER BY on the end is not necessary, as you must set up the
Sorting and Grouping of the report on the same two columns: [Job#]
and Seg. You can leave this off, but if you want to look at the query
results by themselves, this organization of the data would be useful.

Put Label and Value in controls on the report. Group on Job# to put a
heading on each set if desired, or just to leave a little room between
sets.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom

Job# is the ONLY unique key in the table, it is alos the only field
that will have to have something in it. Being this is so do I have to
change the SQL statement? ( i am new to sql and vb ) thanks for your
help

Patti







Tom Ellison said:
Dear Patti:

Please try this instead:

SELECT [Key], 1 AS Seg, "Job#" AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT [Key], 2 AS Seg, "Study" AS Label , [Study] AS Value
FROM Estimates2
WHERE [Study] IS NOT NULL
UNION ALL
SELECT [Key], 3 AS Seg, "Bdrms" AS Label, [Bdrms] AS Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL

The first column above I have changed to read [Key]. This needs to be
a the same column in all 3 parts above. This column (or more than one
column) should be the unique key to this data, something that uniquely
identifies each row. I cannot tell what that would be.

It will be used later to order the results. The results will be
sorted by this [Key], then by Seq (I didn't mean Seg but rather Seq
for "sequence") The way I understand it, when you report the data,
you will need to keep each set of [Job#], [Study], and [Bdrms]
together according to this [Key]. If there is something that labels
each set of [Job#], [Study], and [Bdrms] on the report then that is
probably the [Key] I'm looking for here.

From looking at what you have shown here, I wonder if this [Key] may
be [Job#]. Is that the unique thing separating each set from each
other set?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom

This is what it looks like so far,, however it is not running and I am
getting a syntax error message.

SELECT Estimates2.[Job#], 1 AS Seg,"Job#", AS Label, [Job#] AS Vaule
FROM Estimates2
UNION ALL
SELECT Estimates2.[Study], 2 AS Seg, "Study", AS Label , [Study] AS
Value
FROM Estimates2
Where [Study] IS NOT NULL
UNION ALL
Select Estimates2.[Bdrms], 3 AS Seg, "Bdrms", AS Label, [Bdrms] AS
Value
FROM Estimates2
WHERE [Bdrms] IS NOT NULL
UNION ALL

Can you help??

Thanks again
Patti


Correction:

NOW this makes it a lot clearer.

Sorry,
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Patti:

Not this makes it a lot clearer.

You can move the controls on a form vertically by changing the Top
property of each control (and don't forget the label) when the Current
Event fires. The code would have to calculate this by looking at the
values in the columns that might be null and using a Choose function
to determine the Top value desired:

Me.MyControl.Top = Choose(IIf(IsNull(Me!Column1), 0, 1) +
IIf(IsNull(Me!Column2), 0, 1) , 217, 308, 390)

If the amount each column may move up is constant, something simpler
suggests itself:

Me.MyControl.Top = IIf(IsNull(Me!Column1), 0, 1 +
IIf(IsNull(Me!Column2), 0, 1) * 90 + 217

Here, the position of the Top of the control is 217 when all the
"possibly null" columns above it are null, but it moces down 90 for
each one that is not null.

Here, the Top of the control is 217, 308, or 390, depending on whether
either or both of the RecordSource columns Column1 or Column2 are
null, thus moving this control up one or two "notches" if those
controls disappear. You also need to set the visible property of the
controls that may disappear:

Me.MyControl.Visible = Not IsNull(Me!Column1)
Me.MyLabel.Visible = Not IsNull(Me!Column1)

On a report where you want to do this there will be a lot more work
required. I assume you will want the report to show more than one row
of data, right? But not in columns, rather vertical again.

We have only found this can be done by putting a bound control in
every position where it could occur and then controlling which ones
print. Having the information for the following row of data then
"move up" becomes a problem. The alternative might be to work the
query so it provides both the label and data, something like this:

SELECT UniqueKey, 1 AS Seq, "Label1" AS Label, Column1 AS Value
FROM YourTable
UNION ALL
SELECT UniqueKey, 2 AS Seq, "Label2" AS Label, Column2 AS Value
FROM YourTable
WHERE Column2 IS NOT NULL
UNION ALL
SELECT UniqueKey, 3 AS Seq, "Label3" AS Label, Column3 AS Value
FROM YourTable
WHERE Column3 IS NOT NULL

The UniqueKey needs to be one or more columns that uniquely identify
and sort the original rows in the query to be printed. Be sure to
Group and Sort the report by this (these) column(s) plus the Seq
column. The report can just show the Label and Value from the query
above. You should give your own values for the "Label#" literals and
the actual column and table name.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 30 Dec 2003 03:18:26 -0800, (e-mail address removed) (Patti) wrote:

Dear Patti:

By "hide" what do you mean? And by "blank" what do you mean? Do you
want an entire column in the datasheet of the query to disappear?

Is a "blank" field one that is NULL? Your code seems to indicate that
is what you mean.

So, what do you mean "hide"? Are you viewing multiple rows or just
one? If you are viewing multiple rows in columnar format, and some of
the values in a column are NULL and others are not, surely you cannot
"hide" the whole column without hiding values that are not NULL.

Perhaps if you specify how you "hide the blank fields with VB in a
form or report" this would make more sense.

Tom Ellison
Microsoft Access MVP


Ellison Enterprises - Your One Stop IT Experts


On 29 Dec 2003 08:09:39 -0800, (e-mail address removed) (Patti) wrote:

I need to run a query that will (in the results) hide all blank
fields.
I have tried Expr:IIf[FiledOne] is not null, [FieldOne]). This does
not work.
Any suggestions??

I can hide the blank fields with VB in a form or the report, but I
don't know if I can or how to use VB in a query.

Thank you
Patti

Tom

The table will only ever pull one record at a time. So I need to hide
(not show) these fields in the query results. The fields are NULL.
There are 200+ fields in the table, and I only need to show the fields
that have informaiton in them. The table is set where the Job# is the
primary key and this is what you must enter to pull the needed
information. I have tried the IIf statement as above and a nested IIf
statement as well. I am just learning VB so I am really not that good
at it. In the form, and the report the blank fields do not show,
however I cannot get the the fields with the informaiton to move up to
fill in the blank spaces (on the computer screen) from where the Null
records are hidden. Again any help given will be GREATLY appreciated.

Thank you
Patti
(I am so happy I found this site)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top