Conditional Format


T

Thanks, Chad

Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?
 
Ad

Advertisements

S

strive4peace

you are almost there...

choose --> Expression is

[InputDate] <= DateAdd("m",-6,Date)

assuming the InputDate is your fieldname

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
D

Dirk Goldgar

Thanks said:
Hello, I have a txt box named InputDate on my report and this box gives me
a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it
does
nothing? Im writing it wrong for conditional format?


What does "within the current 6 months" mean to you? Do you mean the 6
months up to the current date? The 6 months after the current date? The 6
months straddling the current date? The 12 months straddling the current
date (6 months before, 6 months after)?
 
F

fredg

Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?

You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.
 
T

Thanks, Chad

Crystal, I have tried that and it does nothing? My field name is InputDate
and I have wrote and tried it like that but it still doesnt change the color
to yellow? Can I do it in the Vba for the detail section.

strive4peace said:
you are almost there...

choose --> Expression is

[InputDate] <= DateAdd("m",-6,Date)

assuming the InputDate is your fieldname

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*



Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?
 
S

strive4peace

Hi (what is your name?)

OOPS! condition has the operator reversed... should be this:

[InputDate] >= DateAdd("m",-6,Date)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*



Crystal, I have tried that and it does nothing? My field name is InputDate
and I have wrote and tried it like that but it still doesnt change the color
to yellow? Can I do it in the Vba for the detail section.

strive4peace said:
you are almost there...

choose --> Expression is

[InputDate] <= DateAdd("m",-6,Date)

assuming the InputDate is your fieldname

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*



Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?
 
Ad

Advertisements

T

Thanks, Chad

Crystal I tried it again and it still didnt work? Fred yours worked
perfectly..... One last question. How do I get the entire row yellow as well?
Thanks!

fredg said:
Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?

You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.
 
S

strive4peace

glad you got a solution <smile>

TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM

put the following textbox control on your form (I usually put it in the
footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it using
the object drop-down (1st icon on the formatting toolbar or combo in the
Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for
a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname (or fieldname) corresponding to your
unique ID.

~~~
I also put a click event on the highlight box to SetFocus to another
control -- because, even though it is not enabled, it will come to the
front and cover everything up if it is clicked on!

~~~~~~~~~

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control that
spans the whole line as you can in a continuous form

* CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR:

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see what
is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left, top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle their
selection status


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*



Crystal I tried it again and it still didnt work? Fred yours worked
perfectly..... One last question. How do I get the entire row yellow as well?
Thanks!

fredg said:
Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?
You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.
 
F

fredg

Crystal I tried it again and it still didnt work? Fred yours worked
perfectly..... One last question. How do I get the entire row yellow as well?
Thanks!

fredg said:
Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?

You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.

Why didn't you ask for that in your original post? You would have
saved lot's of time and effort by those trying to help you.

If you wish to change the entire row in the report, do not use
conditional formatting. Also, your meaning is unclear.

Do you wish to change the backcolor of each individual text and label
control in the detail section, leaving the section color alone?

Make sure each control's BackStyle is set to Normal.
Code the Report's Detail Format event:

If Me.[InputDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.[InputDate].BackColor = vbYellow
Else
Me.Data.BackColor = vbWhite
End If
Dim C As Control
For Each C In Me.Section(0).Controls
If Typeof C is Textbox or Typeof C is Label then
C.BackColor = Me.Data.BackColor
End If
Next

.... or the color of the detail section itself?

Make sure the backstyle of each control in the detail section is set
to Transparent.

Code the Detail Format event:

If Me.[InputeDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.section(0).Backcolor = vbRed
Else
Me.Section(0).Backcolor = vbWhite
End If
 
F

fredg

Crystal I tried it again and it still didnt work? Fred yours worked
perfectly..... One last question. How do I get the entire row yellow as well?
Thanks!

fredg said:
On Tue, 29 Jul 2008 12:59:06 -0700, Thanks, Chad wrote:

Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?

You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.

Why didn't you ask for that in your original post? You would have
saved lot's of time and effort by those trying to help you.

If you wish to change the entire row in the report, do not use
conditional formatting. Also, your meaning is unclear.

Do you wish to change the backcolor of each individual text and label
control in the detail section, leaving the section color alone?

Make sure each control's BackStyle is set to Normal.
Code the Report's Detail Format event:

If Me.[InputDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.[InputDate].BackColor = vbYellow
Else
Me.Data.BackColor = vbWhite
End If
Dim C As Control
For Each C In Me.Section(0).Controls
If Typeof C is Textbox or Typeof C is Label then
C.BackColor = Me.Data.BackColor
End If
Next

... or the color of the detail section itself?

Make sure the backstyle of each control in the detail section is set
to Transparent.

Code the Detail Format event:

If Me.[InputeDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.section(0).Backcolor = vbRed
Else
Me.Section(0).Backcolor = vbWhite
End If

Whoops.

This line
Me.section(0).Backcolor = vbRed
should be
Me.section(0).Backcolor = vbYellow
Else
 
Ad

Advertisements

J

joseph

Good morning FredG,

I hope you see this ...

I tried to follow the code you provided in this post and I came up w/ the
following for my db .. (what I'm looking to do is I have a report with the
following date field "Date Oath Received" ... if that field is blank in my
report I would like to color code the entire row yellow, if not, it can be
white. I would also like to color code the row red if there is a date
populated in that field that is more than 30 days from the date in the "Date
of Appointment" field

This is what I have so far and nothing happens:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= Date And Me.[Date of Appointment] <=
DateAdd("d", -30, Date) Then
Me.[Date Oath Received].BackColor = vbRed
Else
Me.[Date Oath Received].BackColor = vbWhite
End If
Dim C As Control
For Each C In Me.Section(0).Controls
If TypeOf C Is TextBox Or TypeOf C Is Label Then
C.BackColor = Me.[Date Oath Received].BackColor
End If
Next





End Sub

fredg said:
Crystal I tried it again and it still didnt work? Fred yours worked
perfectly..... One last question. How do I get the entire row yellow as well?
Thanks!

:

On Tue, 29 Jul 2008 12:59:06 -0700, Thanks, Chad wrote:

Hello, I have a txt box named InputDate on my report and this box gives me a
list of dates that are within a year. I want to be able to color the date
yellow if its within the current 6 months. the formula I think would be
something like:

<= DateAdd("m",-6,Date) for the "InputDate" conditional format but it does
nothing? Im writing it wrong for conditional format?

You want to use
Between Date() and DateAdd("m",-6,Date())

Select the [InputDate] control.
Click on Format + Conditional Formatting

Set Condition1 to
Field Value is

Then set the expression drop-down to
Between
Next enter
Date()
in the next text dialog box.
Then enter
DateAdd("m",-6,Date())
in the next text box.

Why didn't you ask for that in your original post? You would have
saved lot's of time and effort by those trying to help you.

If you wish to change the entire row in the report, do not use
conditional formatting. Also, your meaning is unclear.

Do you wish to change the backcolor of each individual text and label
control in the detail section, leaving the section color alone?

Make sure each control's BackStyle is set to Normal.
Code the Report's Detail Format event:

If Me.[InputDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.[InputDate].BackColor = vbYellow
Else
Me.Data.BackColor = vbWhite
End If
Dim C As Control
For Each C In Me.Section(0).Controls
If Typeof C is Textbox or Typeof C is Label then
C.BackColor = Me.Data.BackColor
End If
Next

... or the color of the detail section itself?

Make sure the backstyle of each control in the detail section is set
to Transparent.

Code the Detail Format event:

If Me.[InputeDate] >= Date() and Me.[InputDate] <=
DateAdd("m",-6,Date()) Then
Me.section(0).Backcolor = vbRed
Else
Me.Section(0).Backcolor = vbWhite
End If

Whoops.

This line
Me.section(0).Backcolor = vbRed
should be
Me.section(0).Backcolor = vbYellow
Else
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top