If between dates, change colour

[The Bey]

I'm having a report run from a query, and I'm wondering if I can change the backing colour of certain dates on my report via its code.

eg. If Date is between Date() and DateAdd("m", 6, Date())
Me.FieldDate.BackColour = Red

I'd like to do it in code because the query is already asking for criteria from a form

 

[Galaxiom]

Right click on the control and choose Conditional Formatting.

Change the condition to Expression Is and enter this:

[fieldname] >= Date() And [fieldname] <= DateAdd("m", 6, Date())

Set the colours as required

 

[The Bey]

Thanks for the quick response.

Well it didn't want to work The expression I used is

"[InsActionDueDate]>=Date() And [InsActionDueDate]<=DateAdd("m",12,Date())"

where InsActionDueDate is the fields from the query. Is this right, or should I use the column title?

 

[The Bey]

Here's a snapshot of what I've done because it also shows my fields and things. I'm not very good at explaining things so this makes it easier

 

[Galaxiom]

Should work with either the fieldname in the RecordSource query or the Control Name.

Is your date field just text or a DateTime datatype field?

 

[The Bey]

Um it's formatted to General Date and in the field it will only ever have "##/##/####", so only date, no time.

 

[Galaxiom]

Are you sure it isn't working? Everything seems to be correct.

Can you post a cut down version of the database with just the essential objects and some dummy records?
Compact the database and zip it before posting.

 

[The Bey]

Yeah I'll have a go now

 

[The Bey]

Here. A lot of the data has been removed, but the records needed are still present

 

[Galaxiom]

Unfortunately I don't have a lot of experience with Reports. These same settings in a Form would definitely work fine.

I did notice that in the Report View, the Conditional Format background color is honoured when the control of a conforming record is clicked on. There must be another thing we need to know. I tried removing the Alternate Back Color but that made no difference.

Sorry I can't get to the bottom of the problem.

 

[The Bey]

That's ok, thanks for your help anyway