# Solved: MS Access report - Can you highlight Null values?



## Diane Herron (Dec 28, 2000)

I'm familiar with Conditional Formatting in Access reports but can't seem to get this to work. I have a report that returns names, addresses, company names, phones, etc. All I want to do is have it highlight the ones that are null. For example, if a record is missing the phone number, I would like for that phone number field to be yellow.

Seems like it should be "Expression is Null" but it doesn't work.

Thanks!
Diane


----------



## cristobal03 (Aug 5, 2005)

I know this isn't a direct answer to your question, but is there any reason not to use an empty string instead of Null? If a field value equals "" (or *vbNullString*, maybe, though I bet that won't work in CF), the format outcome would be the same. You'd have to do some playing around with it I guess to see if it works 100% of the time. I think there is a way to test for Nulls through CF but it's been so long I can't remember.

Sorry this is just a workaround. HTH anyway.

chris.


----------



## Rockn (Jul 29, 2001)

You will need to do it in VBA and use the onFormat event for the report and enter something like this.

If IsNull(fieldname) Then
Me.ControlName.BackColor = RGB(255,0,0)
Else
Me.ControlName.BackColor = RGB(0,0,0)
End If


----------



## Diane Herron (Dec 28, 2000)

CristalBol03,
Tried the "If a field value equals "" ". It doesn't work.

Rockn,
There is no onFormat event for the report.

If there is anything else I can try, please let me know!

Thanks,
Diane


----------



## cristobal03 (Aug 5, 2005)

Rockn, this might be my misunderstanding because I'm not very clear on the mechanical difference between reports and continuous forms, but wouldn't that format the control unilaterally based on the last control's value? I know that's the behavior if you try to conditionally format a control through VBA in continuous forms.

I don't have Access on this machine to improve my understanding, though.

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump]

Have you tried *IsNull(controlName)* rather than *[control] Is Null*?

I know there's a way to do this; I'm sure I've done it before.

chris.


----------



## Rockn (Jul 29, 2001)

Sorry, the onFormat event is for the details section. I tried it here and it works like a charm.


----------



## Diane Herron (Dec 28, 2000)

CristalBol,
YES! IsNull(controlName) rather than [control] Is Null. It worked!

It's the little things that make me crazy.

Thanks!
Diane


----------



## cristobal03 (Aug 5, 2005)

Glad to help :up:

You can mark this thread "Solved" using the *Thread Tools* at the top of the page.

chris.


----------



## Rockn (Jul 29, 2001)

Glad I could help.....


----------

