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!

 

[vbaInet]

How about 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.

 

[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.

I used:

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

 

[vbaInet]

For Conditional Formatting:

Expression Is
Len([ReportFieldName] & "") = 0

 

[Heatshiver]

I had tried that before in an iif statement and got an error about not completing the argument. Someone else recommended this to fix it:

IIF(Len(Me.ReportFieldName & "") = 0,True,False)

Haven't tried it though...

 

[vbaInet]

I'm pretty sure what I wrote above doesn't contain an IIF() function. Select EXPRESSION IS and write the code as it was given.

 

[Heatshiver]

I tried both the way I was given and what you gave me, but neither worked. Also tried with the iif() statement, but no change.

I did make sure it was EXPRESSION IS as well to no avail.

 

[vbaInet]

Perhaps it's not a zero length string. Did you make include the Default colour as well?

 

[Heatshiver]

Didn't end up using the expression. I used VBA, which helped because I had other parameters I wanted to specify along with the back color.

 

[vbaInet]

Thanks for coming back.