[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
?
315 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
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.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

650 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