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
280 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
  • 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 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

JeffCoachman
2011-03-07-EE-ARG-14-Transcript-.mdb
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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