By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,515 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,515 IT Pros & Developers. It's quick & easy.

Can I bold a field in a report if it meets criteria?

P: 5
Hello All,
I have a report where it lista employees that need to have an eval done in the next 30 days.

If the employee has a 3 month, 6 month or 12 month eval due in the next 30 days it lists the employee name, 3 month eval due date, 3 month eval score, 6 month eval due date, 6 month eval score, and 12 month eval due date and score. I would like the report to bold or highlight either the 3, 6 or 12 month eval due date that made teh ee hit the report criteria, and I cannot seem to get to to work ... So for each ee in the list one of the due dates would be in BOLD or have a red background... (Which i have tried in the ON Open event of the form.

here is my code...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.     Dim strDivision As String
  3.     Dim strTerm As String
  4.     Dim strEmpType As String
  5.     Dim dteCurrentdate As Date
  6.  
  7.     'Open the form to get the variables
  8.     DoCmd.OpenForm "SelectReportCriteriaEval", acNorman, , , , acDialog
  9.  
  10.     ' Get the Seleceted Values
  11.     strDivision = Nz(Forms("SelectReportCriteriaEval")!selDivision, " ")
  12.     strTerm = "N"
  13.     strEmpType = "Temp"
  14.     dteCurrentdate = DateAdd("m", 1, Date)
  15.     'MsgBox "strDivision = " & strDivision
  16.  
  17.     ' Set the recordsource for the report
  18.         If strDivision <> "ALL" Then
  19.             Me.RecordSource = "SELECT * FROM Main_EE_Info " & _
  20.                               "WHERE Division = '" & strDivision & "' " & _
  21.                               "AND EmpType LIKE '[" & strEmpType & "]*' " & _
  22.                               "AND Terminated Like '" & strTerm & "' " & _
  23.                               "AND ((The3MEvalDate <= #" & dteCurrentdate & "# AND The3MEvalScoreFlag = 'N') OR " & _
  24.                               "(The6MEvalDate <= #" & dteCurrentdate & "# AND The6MEvalScoreFlag = 'N') OR " & _
  25.                               "(The12MEvalDate <= #" & dteCurrentdate & "# AND The12MEvalScoreFlag = 'N'))"
  26.         Else
  27.             Me.RecordSource = "SELECT * FROM Main_EE_Info " & _
  28.                               "WHERE EmpType LIKE '[" & strEmpType & "]*' " & _
  29.                               "AND Terminated Like '" & strTerm & "' " & _
  30.                               "AND ((The3MEvalDate <= #" & dteCurrentdate & "# AND The3MEvalScoreFlag = 'N') OR " & _
  31.                               "(The6MEvalDate <= #" & dteCurrentdate & "# AND The6MEvalScoreFlag = 'N') OR " & _
  32.                               "(The12MEvalDate <= #" & dteCurrentdate & "# AND The12MEvalScoreFlag = 'N'))"
  33.  
  34.         End If
  35.  
  36.     'MsgBox "Me.RecordSource = " & Me.RecordSource
  37.  
  38.            ' MsgBox "Me.Date = " & FThe3MEvalDate
  39.  
  40.            '       MsgBox "Me.Date = " & dteCurrentdate
  41.  
  42.     If The3MEvalDate <= dteCurrentdate Then
  43.         If The3MEvalScoreFlag = "N" Then
  44.             Me.FThe3MEvalDate.BackColor = "RED"
  45.         End If
  46.     End If
  47.     If The6MEvalDate <= dteCurrentdate Then
  48.         If The6MEvalScoreFlag = "N" Then
  49.             Me.FThe6MEvalDate.BackColor = "RED"
  50.         End If
  51.     End If
  52.     If The12MEvalDate <= dteCurrentdate Then
  53.         If The12MEvalScoreFlag = "N" Then
  54.             Me.FThe12MEvalDate.BackColor = "RED"
  55.         End If
  56.     End If
  57.  
  58.     DoCmd.Close acForm, "SelectReportCriteriaEval"
  59. End Sub

Any help would be greatly appreciated... I am sure I ma just putting this in the wrong event or somethign but It is Friday and my head hurts so I am posting as a last resort!

THANK YOU!
Feb 15 '08 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
Assuming you're running ACC2000 or later, you can do this using Conditional Formatting, just like you would in a form.

Linq ;0)>
Feb 16 '08 #2

mshmyob
Expert 100+
P: 904
Linq's suggestion is easiest and I would also recommend it but if you still want to code it try the following:

Expand|Select|Wrap|Line Numbers
  1. Dim vRed as Long
  2. vRed RGB(255,0,0)
  3. Me!txtControl.BackColor = vRed
  4.  
Feb 16 '08 #3

Expert Mod 2.5K+
P: 2,545
If you still need to apply bold to a control the hard way without using conditional formatting or setting the background place an expression like this in the on-format event of the section of your report in which the controls you need to highlight are placed:

Expand|Select|Wrap|Line Numbers
  1.  If (condition you are testing) then 
  2.     Me.[name of the control you want to embolden].Fontbold = True
  3. else
  4.     Me.[name of the control you want to embolden].Fontbold = False
  5. Endif
  6.  
If your condition is easily readable and understandable you could shorten this to
Expand|Select|Wrap|Line Numbers
  1.  
  2. Me.[name of the control you want to embolden].Fontbold = (condition you are testing)
  3.  
-Stewart
Feb 16 '08 #4

P: 5
Thanks to all! (I have had the flu and just now gettign back to work)
Feb 21 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help! Hope you're feeling better!

Linq ;0)>
Feb 21 '08 #6

Scott Price
Expert 100+
P: 1,384
Bold-ing a control will make all the text in the control bold, or the color specified. If you are interested in only making one word in a multi-word string bold or a different color, there is a module that I found somewhere and use quite a bit that does just this. If you're interested in it, let me know and I'll dig it out, polish it up and post it :-)

Regards,
Scott
Feb 21 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.