SubReports - would like to condition what appears in field, based on field value, also would like to condition the backcolor

Posted on 2011-03-07
Last Modified: 2012-05-11
Hi EE,

I remember doing something like this in the past, however now i'm having trouble:

The start/end date just show in the database for courses that are clerkships,
            so if the data is present, would like to show the data and color it a light gray
                 if no date info, then the report should show blank for those filelds.

I gave an example below of a function i coded the Me.txtEndDate    (need to do the same for behind Me.txtStartDate)

but for the blank DATES, #ERROR is showing
       for the good DATES, it is not coloring gray....

tx for your ideas, and assistance, below is an mdb, sandra

Private Function f100_FormatEndDate(datEndDate As Date)

If IsNull(datEndDate) Then
   f100_FormatEndDate = vbNullString
   f100_FormatEndDate = " - " & Format([End Date], "mm/dd")
   Me.txtEndDate.BackColor = 14803425
End If

End Function
Question by:mytfein
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35060768
Care to post the exact steps to recreate the issue?

Author Comment

ID: 35061172
Hi Jeff,

tx for writing...

a) in subreport1, there is a field,
                    right before the course title
                                       called Me.txtEndDate

b) i went to the properties window for Me.txtEndDate and typed:

    =f100_formatEndDate([End Date])

c) i coded in vba a function called: f100_formatEndDate that contains:

    Private Function f100_FormatEndDate(datEndDate As Date)

If IsNull(datEndDate) Then
   f100_FormatEndDate = vbNullString
   f100_FormatEndDate = " - " & Format([End Date], "mm/dd")
     Me.txtEndDate.BackColor = 14803425
End If

End Function

d)  what would like is the function should show data and color it gray in the Me.txtEndDate field
      in the report, if [End Date] field contains data.

      instead, what is happening is that if there is no data, the report is showing #error
                                                             if there is      date, the report is not coloring the end date gray

Below are screen shots that describe the above  ...

tx, s
LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 35061861
First, ...FWIW I try not to code anything as vbNullstring
Remember you function says: If Null, then make in a nullstring
(why convert something that is null to a null string?)
IMHO, I would just make it an empty string:   ""

Second, If you want to allow a function argument to be Null then type it as a Variant

Third, if you want to modify the Backcolor, then the BackStyle property must be set to Normal (Yours were set to Transparent)

New sample attached

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 35062371
tx so much Jeff,

could not run the sample bec got error: undefined function Right

so i copied your code to my mdb,  and change the fields from backstyle transparent to normal

a big improvement:
     the #error no longer displays
     blank displays in the end date

a)  the report first comes up in print preview,  and in print preview something strange is happening:
    while most dates are coloring gray nicely
               the first row with dates does not, in fact the end date has a gray border around it

however, when print the report,  the above strangeness does not happen....

b) also, in print preview, the blank dates show blank but when i print the report they are
    showing as gray

    i tried experimenting with
              .backstyle = "transparent"
                            .backstyle = "normal"

          but the #error came up again so took the above out.....

Author Comment

ID: 35062376
tx again, leaving for the day, s
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35091251
Try this, you had missing references.

Also try putting the dash one the end date in a separate label between the two dates and delete the dash from the end date.
This way both dates will display the same.

Author Comment

ID: 35097359
Hi Jeff,

tx... i followed your advice above, and change the source behind Me.txtEndDate to:
=Format([End Date],'mm/dd')

then deleted the f100_function that i had, and instead did an On detail format event like this:

and now preview the report   and print the report.....

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsEmptyControl(Me.txtEndDate) Then
   Me.txtDash = ""
   Me.txtStartDate.BackColor = 16777215
   Me.txtEndDate.BackColor = 16777215
   Me.txtDash = "-"
   Me.txtStartDate.BackColor = 15790320
   Me.txtEndDate.BackColor = 15790320
End If

End Sub

the report preview looks nice.... pls see below....tx again, s
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35097369
OK Great!


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question