CF Expression Help

[xmeb]

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.

>=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

 

[dhookom]

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 ;-)

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

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

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.

DDate > Now()

Thanks.

 

[dhookom]

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

 

[xmeb]

Still does not work. Thanks.

 

[dhookom]

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

 

[xmeb]

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.

 

[dhookom]

Are you sure you have future dates in the data?

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

What do you mean by future dates?

 

[dhookom]

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

 

[xmeb]

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

 

[dhookom]

Set the background color of the text box to something like pink and try again.

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

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

 

[dhookom]

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:

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

 

[xmeb]

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.

 

[dhookom]

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

 

[xmeb]

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!

 

[dhookom]

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

 

[xmeb]

Right. Thanks.

 

[dhookom]

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