Conditional Formatting on Report

[giannisp85]

Hello, I want to create a report to see my apartments availability based on the check in/out dates.
My reports source is a form with all the reservations and I want the apartments name to be highlighted in case the check in/out dates in any record is within today

[BEGIN DATE]<=Date() And [END DATE]>Date()

Even thought the above is working in the form where each record is shown separated, I cant get it to work in the report where I want it to check all records.
Any suggestions please?? Thanks!!

 

[theDBguy]

Hi. Welcome to AWF!

Is your report bound to the same table as your form?

 

[pbaldy]

The report doesn't show any dates. A Null date won't meet your test. You could add a test or use the Nz() function on your date field.

 

[arnelgp]

check the Control Source of [Begin Date] and [End Date]

 

[giannisp85]

Thank you all! The report is bound to the form and the form is bound to the table. It is split database. Regarding showing the dates, i have them as no visible because i only want to see the property status. But even when it is visible it doesn't work. The control source of [Begin Date] and [End Date] seems to be ok, it is taking the values from the report.

Maybe there is a way to do it with VBA when the report loads? Something like this?

Private Sub Report_Load()
If begin_date.Value <= Date And end_date.Value > Date Then
APARTMENT.BackColor = RGB(0, 255, 0)
End If
End Sub

 

[arnelgp]

on your sample report, There is No record that met the condition you set

 

[giannisp85]

Yes it was a coincidence

 

[Gasman]

Thank you all! The report is bound to the form and the form is bound to the table. It is split database. Regarding showing the dates, i have them as no visible because i only want to see the property status. But even when it is visible it doesn't work. The control source of [Begin Date] and [End Date] seems to be ok, it is taking the values from the report.

Maybe there is a way to do it with VBA when the report loads? Something like this?

Private Sub Report_Load()
If begin_date.Value <= Date And end_date.Value > Date Then
APARTMENT.BackColor = RGB(0, 255, 0)
End If
End Sub

Anything in the Load event only happens once?, at Load time, hence it's name.
I would have thought you would need an event like On Format, or On Print. Perhaps On Paint ?

 

[pbaldy]

Also make sure your date values don't include a time component. That would cause the begin date to fail your test if it was on the same day.