INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

CF Expression Help

CF Expression Help

(OP)
Hi:

I have searched on Google for what I want to do but I have found nothing that is exactly the same so far.

On a report I have a subreport that has a "PrimiaryLocation" text box. I use the following in the subreport query criteria for my field "DDate" to show primary locations for the last five days which works fine.


CODE -->

>=DateAdd("d",-1,Date())-5 

What I would like to do using CF is to have primary locations that match todays date be a different color and those that are two days old be a different color.

Thanks,

xmeb

RE: CF Expression Help

I'm not sure why you can't spell out "conditional formatting" at least once so everyone understands what you are referencing in your question. It isn't until your last sentence that it becomes somewhat clear winky smile

What have you tried? Did you use "Expression Is"?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
Sorry, I will write it out next time.

I have tried to use "Expression Is" but I do not know how to write the expression. I tried ones like the following but they do not work.

CODE -->

DDate > Now() 

Thanks.

RE: CF Expression Help

I would make sure the Expression Is contains [DDate]>Now() with the []s around the field name. Also the text box background can't be transparent if you expect the background colors to display.

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
Still does not work. Thanks.

RE: CF Expression Help

Quote (xmeb)

Still does not work
This doesn't tell us much. Can you get any format changes in any control in any report? Do you have DDate bound to a text box in the subreport?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
I tested a "field value is equal to" expression on the same text box in the same subreport and it worked fine. There is a text box bound to "DDate" in the subreport. Thanks.

RE: CF Expression Help

(OP)
What do you mean by future dates?

RE: CF Expression Help

Future dates are date values in the future rather than in the past. Are any of your [DDate] values later than the current date/time?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
No and I put in a date for next month but it still does not work. Thanks.

RE: CF Expression Help

(OP)
Still not working. I have conditional formatting on a number field in the same subreport and it works fine. Thanks.

RE: CF Expression Help

I don't understand why conditional formatting isn't working. Is the control actually a text box?

As an alternative try use code in the On Format event of the section of the subreport containing the controls like:

CODE --> vba

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.DDate > Now() Then
        Me.PrimaryLocation.BackColor = vbYellow
     Else
        Me.PrimaryLocation.BackColor = vbWhite
    End If
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
It is a text box and that does not work either. It must be something to do with the "Now()" because other conditional formatting works fine on the same text box in the same subreport. Thanks.

RE: CF Expression Help

If the date is date only with no time element then use Date() rather than Now(). When you enter the code in VBA, the () may be removed.

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
Date does not work either. I must have built something wrong. I will keep working on it and let you know if I figure it out. Thanks!

RE: CF Expression Help

When you look at the data source of your subreport in datasheet view, is the column DDate aligned left or right?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
Right. Thanks.

RE: CF Expression Help

Are you looking at the Print Preview mode? If so, I am running out of suggestions and would actually need to see the file.

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
What is Print Preview mode? I right click the table and select open, is that what you mean? I just created a new blank report and put "DDate" and "PrimaryLocation" text boxes on it and put in the same conditional formatting that is giving us trouble and it still does not work. However, other types of conditional formatting work fine. Thanks.

RE: CF Expression Help

You can view reports in Design View, Report View, Layout View, and Print Preview.

Can you copy and paste your actual Expression Is in a reply here?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
I checked it in Print Preview.

[DDate]>Now()

RE: CF Expression Help

(OP)
File is confidential and too big to be stripped and sent. I have been doing research and found that controls and fields cannot have the same names for conditional formatting to work which mine did. I changed them but it still does not work. Thanks for all your help.

RE: CF Expression Help

Have you tried creating a very small sample database with just the records and report needed to test?

You haven't created a user-defined-function named DDate() have you?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
I will create the small database as you suggested tomorrow.

I do not think I have created a user-defined-function named DDate() but I will search for it.

Thanks.

RE: CF Expression Help

(OP)
I built a database with a table (two fields), a query, and a report. I used "DDate" and several other field names and it does not work. Other types of conditional formatting work fine.

RE: CF Expression Help

xmeb,
Is there any way you could post a copy of your file some place we could get it?

Duane
Hook'D on Access
MS Access MVP

RE: CF Expression Help

(OP)
Yes, the test one I built I can share. How is that best done? Thanks.

RE: CF Expression Help

There are a number of file sharing sites like the one mention at the bottom of the reply screen. You can also find my email in the Hook'D on Access web site in my signature.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close