Format Text Box Based on Condition

[ConfusedNAccess]

How can I format at text box, or field on a report to remain blank if the corresponding field is null?

I don't want data in one field to appear if data in any of the corresponding fields are blank, or have null values

Any assistance would be great!

 

[THWatson]

ConfusedNAccess
On the OnFormat event for the report, try something such as the following...

If IsNull(YourField) Then
Field1.Visible = False
Field2.Visible = False
Field3.Visible = False
Else
Field1.Visible = True
Field2.Visible = True
Field3.Visible = True
End If

Tom

 

[ConfusedNAccess]

How would I write this based on multiple fields?

If IsNull([Jan] or [feb] or [Mar]) Then
NameofReport.visible = False
Field2.Visible = False
Field3.Visible = False
Else
Field1.Visible = True
Field2.Visible = True
Field3.Visible = True
End If

is this right? and i'm sorry.. i'm new to this. where do i put this code?

 

[THWatson]

ConfusedNAccess
Take a look at the first line in your code.
If IsNull([Jan] or [feb] or [Mar]) Then

Change that line to
If IsNull([Jan]) Or IsNull([feb]) Or IsNull([Mar]) Then

As for where to put the code, my suggestion in the first reply was to put the code on the OnFormat event for the Detail section of the report. That still holds...except for the NameofReport control. Is the NameofReport control in the Detail section?

However, are Jan, feb and Mar the actual names of your fields? If those are the names, it might be easy to confuse the field names with months in dates.

Anyway, try what I have suggested, and let me know.

If you are still having trouble, it will help if you post back the actual code, and where you have it located.

Tom

 

[ConfusedNAccess]

I put this in the section: detail property on format spot--
I can't get it to work.

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([Jan]) Or IsNull([Feb]) Or IsNull([Mar]) Then
SheetRowText.Visible = False

End If



End Sub

 

[THWatson]

ConfusedNAccess
I see in your original post you say "if the corresponding fields are blank or have null values"

You might have to add to the code to have Access look for blank fields as well as null values.

Something such as...
If IsNull([Jan]) Or ([Jan]="") Then

You might also add the Keyword Me in front of the fields. Such as...
If IsNull(Me.Jan) Or (Me.Jan = "") Or IsNull(Me.Feb) Or (Me.Feb = "") Then

Tom

 

[ConfusedNAccess]

Now the sheetrowtext field is missing when it should be there. next, how do I allign all the fields to show up as if they were in one column? instead of spread all the way across?

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.Jan) Or IsNull(Me.Feb) Or IsNull(Me.Mar) Or IsNull(Me.Apr) Or IsNull(Me.May) Or IsNull(Me.Jun) Or IsNull(Me.Jul) Or IsNull(Me.Aug) Or IsNull(Me.Sep) Or IsNull(Me.Oct) Or IsNull(Me.Nov) Or IsNull(Me.Dec) Then
SheetRowText.Visible = False
Jan.Visible = False
Jan_Label.Visible = False
Feb.Visible = False
Feb_Label.Visible = False
Mar.Visible = False
Mar_Label.Visible = False
Else
SheetRowText.Visible = True
Jan.Visible = False
Jan_Label.Visible = False
Feb.Visible = False
Feb_Label.Visible = False
Mar.Visible = False
Mar_Label.Visible = False

End If



End Sub

 

[THWatson]

ConfusedNAccess
Take a look at the part of your code following the Else line. Those lines should have True values in order to show.

To line things up vertically rather than horizontally, place the fields under one another instead of beside one another.
In other words...
Jan field
Feb field etc.
rather than...
Jan field Feb field Mar field etc.

Unless this is a report based on a Crosstab query. Then it takes a different approach to be able to line them up vertically.

Tom

 

[THWatson]

ConfusedNAccess
Just thought of something...

Is it really the case that if any one of those fields is Null you want nothing at all to show? The way you have it now is if any field from Apr through Dec is Null, then nothing will show in Jan, Feb or Mar.

Tom

 

[ConfusedNAccess]

I see. Ok.. any one of the fields jan-dec could have a date listed, or they could all be blank. I need the sheetrowtext and the date to show if only if a date is present in those fields.

If nothing is listed in Jan-Dec, then I don't need anything to appear.

 

[THWatson]

Yes, but the way you have the code now is if any one of the fields is blank then nothing shows in any of the others.

In other words, you could have data in all the fields up to November, but if December is Null or blank nothing will show.

In any event, I have to go now for several hours. Post back if you need to, but I won't be able to look at it until later on this afternoon or this evening.

Tom

 

[ConfusedNAccess]

thank you for your help. If yu have more time, I'd appreciate your guidance! You've been very helpful!

 

[THWatson]

ConfusedNAccess
Post back with further questions when you wish.

Tom

 

[ConfusedNAccess]

Good Morning!

I appreciate your help more than you know! How can I set up the code, or how should I write it to reflect,

if a date is present in fields jan-dec, to show the name of the report (sheetrowtext)?

I've tried setting the query up differently but evidently I don't know what I'm doing.

Thanks again,
Meryn

 

[THWatson]

Meryn
I'm still a bit unclear as to what you are aiming for.

You want the report name (sheetofrowtext) to show only if there is data in all of the months January to December, and the report name to be hidden otherwise?

Let me know...
1. Exactly what your report is supposed to do.
2. What you are getting with the setup below.

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.Jan) Or IsNull(Me.Feb) Or IsNull(Me.Mar) Or IsNull(Me.Apr) Or IsNull(Me.May) Or IsNull(Me.Jun) Or IsNull(Me.Jul) Or IsNull(Me.Aug) Or IsNull(Me.Sep) Or IsNull(Me.Oct) Or IsNull(Me.Nov) Or IsNull(Me.Dec) Then
SheetRowText.Visible = False
Jan.Visible = False
Jan_Label.Visible = False
Feb.Visible = False
Feb_Label.Visible = False
Mar.Visible = False
Mar_Label.Visible = False
Else
SheetRowText.Visible = True
Jan.Visible = True
Jan_Label.Visible = True
Feb.Visible = True
Feb_Label.Visible = True
Mar.Visible = True
Mar_Label.Visible = True

End If

End Sub

By the way, you should always use Option Explicit as well as Option Compare Database. That forces you to declare any variables that you may use.

Tom

 

[ConfusedNAccess]

I need the name of the report (sheet row text) to appear only if ANY of the fields Jan-Dec have a date present.

I got a compile error: Invalid Outside procedure. Not sure what this means

 

[THWatson]

Meryn
Okay, then the Or needs to be changed to And.

Let's make the code a bit shorter and simpler and see what happens. Try the following...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.Jan) And IsNull(Me.Feb) And IsNull(Me.Mar) And IsNull(Me.Apr) And IsNull(Me.May) And IsNull(Me.Jun) And IsNull(Me.Jul) And IsNull(Me.Aug) And IsNull(Me.Sep) And IsNull(Me.Oct) And IsNull(Me.Nov) And IsNull(Me.Dec) Then
SheetRowText.Visible = False

Else
SheetRowText.Visible = True

End If

End Sub

Have you always been getting the Compile error, or just now? It probably indicates that something is misspelled. Does it point to a particular line in the code when the error message appears?

You could also try putting Me in front of SheetRowText.
e.g. Me.SheetRowText.Visible = True

Tom

 

[ConfusedNAccess]

The compile error has gone away, evidently I spelled something wrong, and corrected it. Does it matter if the report is grouped? Does this effect anything??

This is so frustrating. I'm still putting the code in the detail event section.

 

[THWatson]

Meryn
I assume your grouping is by quarters, or something such as that. But the grouping is in the Detail Section, so that shouldn't affect whether or not the SheetRowText text box is visible.

However, it seems to me that I don't know enough about your setup, so I am at a bit of a loss to know what to suggest next.

Issues...
(1) I assume that your report is based on a query or table, and that the fields Jan, Feb, Mar...through to Dec are actual fields in the table. Is that correct?
(2) What other fields are in the report?
(3) This is a report...not a subreport...right?

Tom

 

[THWatson]

Meryn
Just trying to think through some stuff here...

1. What type of values go in the Jan, Feb, Mar etc. fields? Are they numbers or text?
2. If there is no data entered in those fields, Jan through Dec, what shows? Nothing (a Null value), a 0 possibly?
3. Where does the report name (SheetRowText) come from?
4. You say the months are grouped. If, for example, you put Jan, Feb and Mar in a group, and that comes from a Totals query, then unless you have the Jan, Feb and Mar fields in the report itself (visible or hidden) you can't refer to them in code because Access can't see them.

I was also thinking that, actually, why do you need the code at all? If you want the report name to be visible if there is data in any one of the Jan through Dec fields, then would you ever run a report where there is no data in all of those fields? If you wouldn't ever do that, then you don't need all the code at all. If you would do that, then you can put code on the No Data event for the report so that a message comes up indicating there is no data to report.

If none of what I have said corrects anything, then if you are interested in sending the database to me to look at, I can let you know how to do that.

Tom