?
Solved

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
8
Medium Priority
?
308 Views
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
Else
   f100_FormatEndDate = " - " & Format([End Date], "mm/dd")
   
   Me.txtEndDate.BackColor = 14803425
End If

End Function

 2011-03-07-EE-ARG-14-Transcript-.zip
0
Comment
Question by:mytfein
[X]
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
8 Comments
 
LVL 74

Expert Comment

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

Author Comment

by:mytfein
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
Else
   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
2011-03-07-endDate.GIF
2011-03-07b-endDate.GIF
2011-03-07c-endDate.GIF
2011-03-07d-endDate.GIF
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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

JeffCoachman
2011-03-07-EE-ARG-14-Transcript-.mdb
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:mytfein
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

however:
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.....
2011-03-07-gray.GIF
0
 

Author Comment

by:mytfein
ID: 35062376
tx again, leaving for the day, s
0
 
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.
2011-03-07-EE-ARG-14-Transcript-.mdb
0
 

Author Comment

by:mytfein
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
Else
   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
2011-03-11-rpt.GIF
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35097369
OK Great!

;-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 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