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

Need help with a conditional formatting string

P: n/a
I am trying to do some conditional formatting with a textbox and I am
not able to quite get it.

Here is the setup:

My record includes a textbox called [Due_Date] and two checkboxes
the first called [Completed] the second called [Transfered].

I would like the [Due_Date] box to set the background to RED if the
[Due_Date] is less than today's date and [Completed] and [Transfered]
both equal False.

I have tried to patteren my code after an example in the Access
Developers book, but I haven't adapted it correctly. Here is what I
did:

Dim fcd As FormatCondition
Dim txt As TextBox

Set txt = Date_Due
With txt
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual,
"[Completed] = false and [Transfered] = false and " & "#" & Format(txt,
"mm-dd-yyyy") & "# < #" & Format(Date, "mm-dd-yyyy") & "#")
fcd.BackColor = vbRed
End With
End With
It works when I take out the Date condition, and just evaluate the
checkboxes, so I am pretty certain that my problem is with the way I am
trying to evaluate the date.

Any help would be appreciated.

Thanks,

Jody Blau

Mar 4 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know where you're getting that FormatCondition object, it isn't
in Access 2002. Anywho..., you can do something like this:

Private Sub ChangeDisplay()
' Change the background color of the control Due_Date
' when Completed and Transfered are checked and
' when Due_Date < Date().

If CDate(Me!due_date) < Date
AND Me!Completed = False
AND Me!Transfered = False Then
Me!due_date.Backcolor = vbRed
Else
Me!due_date.Backcolor = vbWhite
End If

End Sub

Call this routine from the form's OnCurrent event and from the controls
Due_Date, Completed and Transfered AfterUpdate event ('cuz if they
change you want the background color to change).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAkDToechKqOuFEgEQJs4ACgso2KuJjHTtl0wnffzUn7CO VgiK4AoL7/
rntp8tJ5RCK1uVjdlPY2rF/Q
=dkUM
-----END PGP SIGNATURE-----

jodyblau wrote:
I am trying to do some conditional formatting with a textbox and I am
not able to quite get it.

Here is the setup:

My record includes a textbox called [Due_Date] and two checkboxes
the first called [Completed] the second called [Transfered].

I would like the [Due_Date] box to set the background to RED if the
[Due_Date] is less than today's date and [Completed] and [Transfered]
both equal False.

I have tried to patteren my code after an example in the Access
Developers book, but I haven't adapted it correctly. Here is what I
did:

Dim fcd As FormatCondition
Dim txt As TextBox

Set txt = Date_Due
With txt
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual,
"[Completed] = false and [Transfered] = false and " & "#" & Format(txt,
"mm-dd-yyyy") & "# < #" & Format(Date, "mm-dd-yyyy") & "#")
fcd.BackColor = vbRed
End With
End With
It works when I take out the Date condition, and just evaluate the
checkboxes, so I am pretty certain that my problem is with the way I am
trying to evaluate the date.

Any help would be appreciated.

Thanks,

Jody Blau

Mar 4 '06 #2

P: n/a
I suppose I left out one piece of information. The form is a
Continuous form, so if I use the method you described, then if the
condition is true for one record, it will change the background fro
every record being displayed. (unless I am mistaken).

Oh, the FormatCondition object that I reference is part of th Microsoft
Access 10.0 Object Library.

The Acess 2002 develooper's book mentions that I should use the "#"
marks for dates because the expressions used with the FormatCondition
object are strings and not variants.

Anyway, any idea about how I can adjust the syntax in my string
expression so that it would make sense?

Thanks,

Jody Blau

Mar 4 '06 #3

P: n/a
I have been troubleshooting this some more and while doing so have
removed the parameters for Completed and Transfered and am attempting
to simply get the date part to work.

When I set it as follows, it is dispalying every record as red even if
the condition isn't met for that record:

Set txt = Date_Due
With txt
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual, "#" &
Format(Date_Due, "mm-dd-yyyy") & "# < #" & Format(Date, "mm-dd-yyyy")
& "#")
fcd.BackColor = vbRed

End With
End With

I'm out of ideas on how to do this correctly.

Any thoughts?

Jody

Mar 4 '06 #4

P: n/a
I finally figured it out, and am posting my results just in case it
helps anyone else:
Set txt = Date_Due
With txt
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual, "Date_Due <
#" & Format(Date, "mm-dd-yyyy") & "#")
fcd.BackColor = vbRed

End With
End With
The Access development book led me to think that I needed to use the
"#"'s and the Format() stuff on the text box. I don't know if there
are situations in which you do have to do that, but in my case using
them caused the formatcondition not to work properly.
Jody

Mar 4 '06 #5

P: n/a
jodyblau wrote:
I suppose I left out one piece of information. The form is a
Continuous form, so if I use the method you described, then if the
condition is true for one record, it will change the background fro
every record being displayed. (unless I am mistaken).

<SNIP>

When you change the format of a control on one row of a Continuous Form
all other rows will change the same control. This is because you are
changing the format in the Form not the record. Since the same form is
used to display all the records (even if they are one on top of
another), all the records will show the change.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Mar 4 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.