mytfein
asked on
SubReports - would like to condition what appears in field, based on field value, also would like to condition the backcolor
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(datEndD ate 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
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(datEndD
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
Care to post the exact steps to recreate the issue?
ASKER
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(datEndD ate 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
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(datEndD
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
tx again, leaving for the day, s
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
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
ASKER
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.txtEndDa te) 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
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.txtEndDa
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
OK Great!
;-)
;-)