Highligting certain record in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to get Access to highlight specific records in a report
without grouping them differently from the rest of the records. One could
e.g. use a date for when a record was added and get the database to mark that
record with a color based on a comparison between (now) and (added date + a
number of days). Does anyone know how to setup such an solution in practice?

Thanks in advance / Bell
 
If you have Access 2000 or newer, open the report in design view, right
click the desired control(s) and choose Conditional Formatting. Set the
parameters as desired. If you want the entire row highlighted, it may be
easier to set the background of all of the controls to Transparent then
place an unbound textbox behind them. Set the conditional formatting on this
textbox. Since the other controls are transparent, the settings from this
new textbox will show through.

Another option is to use the Format event of the section (probably the
Detail section) and set the background color of the section to the desired
color when a field meets certain criteria. Again, you would need the
controls to have transparent backgrounds so that the color change in the
section's background will show through.

Example:
If DateDiff("d", "#" & Me.txtDate & "#", Date) >5 Then
Me.Section(acDetail).BackColor = vbRed
Else
Me.Section(acDetail).BackColor = vbWhite
End If

You can also open the properties of the section, go to the BackColor
property and pick the desired colors. Copy and paste the resulting numbers
into the code in lieu of vbRed and vbWhite.
 
Thank you using a textbox worked out perfectly. In fact I decided to use two
texboxes, one for another event. In order for this to work however I need to
make the first textbox transparent if the condition is not met but I cant't
figure out how to do this, perhaps I could use an expression is in the
condtion field but I can't get find an expression Access accepts. I've tried
with numerous variations of this statement; IIf([PaymentDate]>Now()-30, " ","
") but Access keeps complaining. Any suggestions?

Once again thanks / Bell

"Wayne Morgan" skrev:
 
If you are trying to put the expression in the Condition Box of the
Conditional Formatting dialog, the expressing must evaluate to True or
False. If that's not where you're putting it, please explain further.
 
Ok. Using a true or false function will probably not be enough. What I'm
trying to now is ti get the first textbox to become yellow when a record meet
the qriteria of Now()-5 compared to the date when the record was added to the
database. When that time expires the texbox becomes white against a white
background and is hence not visible. Behind this textbox I've placed a second
one that I want to become colored when a record (actually an invoice) is
paid. The problem I have is that this second box is hidden behind the first
one no matter if its condition is met or not and if I could only make this
first one transparent when the condition is not met it would have solved my
problem. Any suggestions?

Best regards / Bell

"Wayne Morgan" skrev:
If you are trying to put the expression in the Condition Box of the
Conditional Formatting dialog, the expressing must evaluate to True or
False. If that's not where you're putting it, please explain further.

--
Wayne Morgan
MS Access MVP


Bell said:
Thank you using a textbox worked out perfectly. In fact I decided to use
two
texboxes, one for another event. In order for this to work however I need
to
make the first textbox transparent if the condition is not met but I
cant't
figure out how to do this, perhaps I could use an expression is in the
condtion field but I can't get find an expression Access accepts. I've
tried
with numerous variations of this statement; IIf([PaymentDate]>Now()-30, "
","
") but Access keeps complaining. Any suggestions?
Click to expand...
Click to expand...
 
Your equation does return True or False, it is the IIf() that doesn't.
You've told it to return " " whether the item is True or False.

Instead of:
IIf([PaymentDate]>Now()-30, " "," ")

Try:
[PaymentDate]>Now()-30
This is the part that is evaluating to True or False inside the IIf(), just
use it.

You could also use:
IIf([PaymentDate]>Now()-30, True, False)
but there is no need to add the IIf() function here.

--
Wayne Morgan
MS Access MVP


Bell said:
Ok. Using a true or false function will probably not be enough. What I'm
trying to now is ti get the first textbox to become yellow when a record
meet
the qriteria of Now()-5 compared to the date when the record was added to
the
database. When that time expires the texbox becomes white against a white
background and is hence not visible. Behind this textbox I've placed a
second
one that I want to become colored when a record (actually an invoice) is
paid. The problem I have is that this second box is hidden behind the
first
one no matter if its condition is met or not and if I could only make this
first one transparent when the condition is not met it would have solved
my
problem. Any suggestions?

Best regards / Bell

"Wayne Morgan" skrev:
If you are trying to put the expression in the Condition Box of the
Conditional Formatting dialog, the expressing must evaluate to True or
False. If that's not where you're putting it, please explain further.

--
Wayne Morgan
MS Access MVP


Bell said:
Thank you using a textbox worked out perfectly. In fact I decided to
use
two
texboxes, one for another event. In order for this to work however I
need
to
make the first textbox transparent if the condition is not met but I
cant't
figure out how to do this, perhaps I could use an expression is in the
condtion field but I can't get find an expression Access accepts. I've
tried
with numerous variations of this statement; IIf([PaymentDate]>Now()-30,
"
","
") but Access keeps complaining. Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top