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
258 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now