Referencing Table Field Value to Change Back Color (with if/iif VBA/expression)

[Heatshiver]

I have a report where some field values will be blank at times. I would like to write an if statement (in VBA or as an expression) to state that if the field is blank, to change the back color of the field value on the report.

I can't seem to get it to work in either VBA or as an expression. In VBA I get a debugging error as to how I reference the table field value. In an expression (I am not sure how to refer to the back color of a field) I first tested with true returning "Good" and false returning "Bad". Even though the table field value is blank, it returns "Bad". I even tried to include IsNull(""), but this still returns "Bad". Even if I put a value in the table field and refer to that in the expression, it still returns "Bad" instead of "Good".

Can anyone provide me an example on how this would be done?

At the moment I don't have a date selection form before the report, but I have a record in the table that shows when switching the report to View mode.

Thank you!

[RuralGuy]

Why not just use Conditional Formatting?

[Heatshiver]

Thanks for the reply.

This looks like what I need, but I still am unsure what I can use in the expression area to state that when the field is blank use the color.

I tried "" and used Nz without success.

[RuralGuy]

To begin with, reference the control on the report and not the field in the table. Try something like:
IIF(Len([YourControl] & "") =0) ... in the conditional format.

[Heatshiver]

Thanks for the help.

I tried your expression, but it stated that it had the wrong number of arguments.

[Heatshiver]

Oddly enough just found a similar post you replied in from 2009: http://www.access-programmers.co.uk/...d.php?t=168241

[Heatshiver]

I tried your VBA code from the 2009 post as follows:

If Len(Me.DailyEndDepth & "") = 0 Then
Me.DailyEndDepth.BackColor = vbBlack
End If

When I click View for my report, it gives me a debugging error that states "The expression you entered has a field, control, or property name that Microsoft Access can't find." And it highlights the words I have in red.

[Heatshiver]

Figured it out for the VBA (still not sure what's up with the expression).

I had been putting it in the Open event of the report, but it needed to be on the Load event.

Thanks for the help.

[RuralGuy]

Glad to hear you got it sorted. The Conditional Format expression is probably:
IIF( Len(Me.DailyEndDepth & "") = 0,True,False)