Hello All
I tried this multiple ways and nothing is helping.
I have a report based on a crosstab query to allow me to show 2 rows
on one line - query look and work ok. When I creat a report, all
seems ok, until I try to only show the labels where the underlying
field is not empty or not null:

I have used
Me.lblAM.Visible = Not (IsNull(Me.txtAM))
Me.lblPM.Visible = Not (IsNull(Me.txtPM))
+
If Me.Text16 = 1 Then
Me.lblAM.Visible = False
else
Me.lblAM.Visible = True
End If

Both in the format & print sections of the report.

Any suggestions would be appreciated if there is anything else obvious
that I'm missing - or perhaps the crosstab is the cause?
Thanks
John

It would help if you included all the code and also told us what the problem
is. Do you get a syntax error? The label controls are always visible? The
label controls are never visible?

Perhaps you are executing the code in the wrong section of the report? Are
you executing the code in the section where the labels are located (as a
guess, the detail section)?

Is it possible that txtAM and txtPM are zero-length strings? You can handle
zero-length strings, nulls, and strings that have multiple spaces using
expressions like the following:

Me.lblAM.Visible = Len(Trim(me.txtAM & "")) > 0
Me.lblPM.Visible = Len(Trim(me.txtPM & "")) > 0


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 6/20/2011 9:46 AM, flymo wrote:

On Jun 20, 10:55*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Hello John
Yes I am using the detail section of the report and I have tried the
format & print events.
The labels are always visible regardless of what ever code I attach to
the events I also tried your attached lines without success.

I recreated another report in case the initial one was corrupted in
some way, unfortunately I get the same result.

I set the crosstab up to show
___________AM__PM
name date___X___X

I am trying to place the lblAM over txtAM if txtAM is not null the
label shows and covers the txtbox, if the txtAM is null then the txt
and label are hidden.

Hope this clarifies
Regards
John

On Jun 20, 10:55*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Hi John
There is no errors when I run the report and I have applied the code
to the print and format events of the report (in the detail section) I
also tried your suggestion without success. The labels are always
visible regardless of how I code the event.

What I am trying to get from the crosstab is the query picks up any
rows for a date and a session (AM,PM,EVE) and should look like

________________AM__PM
Name____Date X X

When the report opens I want to see that if there is a value in txtAM
then a label lblAM placed over the txtbox becomes visible and the
txtbox is hidden.

I have done this before with other reports and I cannot see where this
wont work, unless it is tied to the crosstab query.

Hope this clarifies.
John

I'm not certain what you mean by


If the crosstab is, in fact, returning the values you show in the lines
following "I set the crosstab up to show" when the time is not available or
the other information is not available, the field is neither a zero-length
string nor is it null, and the textbox displaying it will not be, so neither
your IF nor John's would be applicable. I agree with him that you need to
show the details.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"flymo" <fly_mo (AT) hotmail (DOT) com> wrote

On Jun 20, 10:55 am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Hello John
Yes I am using the detail section of the report and I have tried the
format & print events.
The labels are always visible regardless of what ever code I attach to
the events I also tried your attached lines without success.

I recreated another report in case the initial one was corrupted in
some way, unfortunately I get the same result.

I set the crosstab up to show
___________AM__PM
name date___X___X

I am trying to place the lblAM over txtAM if txtAM is not null the
label shows and covers the txtbox, if the txtAM is null then the txt
and label are hidden.

Hope this clarifies
Regards
John

On Jun 20, 1:39*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Thanks Larry
I think I understand what is being asked for.

The query has the fields
Row - Name, Date
Column - session (can be AM,PM or EVE)
Count Value - Name
The crosstab actually shows in the report:

_____________AM__PM (this is the page header)

Name |Date | 1 | 1 |
Name |Date | 1 | |
Name |Date | 1 | 1 |
Name |Date | 1 | 1 |
Name |Date | |1 |

What I want to show is:

Name |Date | AM | PM |
Name |Date | AM | |
Name |Date | AM | PM |
Name |Date | AM | PM |
Name |Date | | PM |

When I try to apply null or "" or if txtAM >0 or txtAM = 1
the labels appear regardless of any condition set.


Hope this clarifies.
regards
John

flymo wrote:
with something like this:

Select [name], [date]
, iif(SUM(iif([session]='AM',1,0))>0,'AM','') AM
, iif(SUM(iif([session]='PM',1,0))>0,'PM','') PM
, iif(SUM(iif([session]='EVE',1,0))>0,'EVE','') EVE
FROM yourtable
GROUP BY [name], [date]

Alternatively, you could base a new query on the saved crosstab and do the
similar IIF logic:
Select [name], [date]
, iif([AM]>0,'AM','') AM
etc
FROM your_saved_crosstab_query

On Jun 20, 2:56*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Thanks Bob I will try that,
Much appreciated
John

Name and Date are Access Reserved Words and should not be used as Field
Names. (But I don't think that is the cause of your problem.) It is better
to use more specific names that are not Access reserved words, like
AttendeeName, or ClassDate.

Can you copy the SQL of your cross-tab query and paste it into a post here?

Access can sometimes be a little "too helpful" by not including fields of
the Record Source unless they are specifically used as Control Source of a
Control in the Report. Perhaps that's why you are referring to the Controls
(e.g., textbox) in your code, and, of course, a CrossTab query can be
non-trivial to understand. I usually try to move any calculations involving
data into the Query that might be omitted from the RecordSource of the
Report because they are not the Control Source of a Control if I can, so I
don't "stumble over myself"

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"flymo" <fly_mo (AT) hotmail (DOT) com> wrote

On Jun 20, 1:39 pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Thanks Larry
I think I understand what is being asked for.

The query has the fields
Row - Name, Date
Column - session (can be AM,PM or EVE)
Count Value - Name
The crosstab actually shows in the report:

_____________AM__PM (this is the page header)

Name |Date | 1 | 1 |
Name |Date | 1 | |
Name |Date | 1 | 1 |
Name |Date | 1 | 1 |
Name |Date | |1 |

What I want to show is:

Name |Date | AM | PM |
Name |Date | AM | |
Name |Date | AM | PM |
Name |Date | AM | PM |
Name |Date | | PM |

When I try to apply null or "" or if txtAM >0 or txtAM = 1
the labels appear regardless of any condition set.


Hope this clarifies.
regards
John

On Jun 20, 3:04*pm, flymo <fly... (AT) hotmail (DOT) com> wrote:
A couple of minor adjustments and it worked like a charm.
Many thanks, that was a simpler solution than the one I was trying to
mangle.

Regards
John