Conditional Formating for Empty Date Field

[RexesOperator]

I have taken a look around for this, but all I've found, so far, are examples for dates and date ranges. I want to use conditional formatting to change the background colour of a date field when no date has been entered.

How do I specify if the field data for a date field has no value? I have tried Null and 0, and a few other things, but I can't seem to get this to work.

 

[boblarson]

Try changing to Is Null instead of just Null.

 

[RexesOperator]

It doesn't work on either Expression or Field Value.

This form is not used for entering data - just for displaying it.

I've even tried code:

Private Sub Form_Open(Cancel As Integer)
'BackColor field property changes to white when data is entered

If Not IsNull(txtDATESENT) Then
txtDATESENT.BackColor = 16777215
End If

End Sub

 

[dkinley]

Make sure the back style property is not set on transparent. If it is, it won't matter what color you assign to it - it will never be seen.

-dK

 

[RexesOperator]

Back style is set to normal.

 

[dkinley]

Hmmm. Bit me before ...

I just tried the following ....

If Len(Nz(Me.txtControlName, "")) = 0 Then
    Me.txtControlName.BackColor = 255
End If

And it worked fine.

-dK

 

[boblarson]

It doesn't work on either Expression or Field Value.

This form is not used for entering data - just for displaying it.

I've even tried code:

Private Sub Form_Open(Cancel As Integer)
'BackColor field property changes to white when data is entered

If Not IsNull(txtDATESENT) Then
txtDATESENT.BackColor = 16777215
End If

End Sub

The code you just supplied will not change the back color if the control is null. And you can't use that in a form's OPEN event because the recordset has not yet loaded. The Load event comes AFTER the Open event.

What is the exact expression you've used in the conditional formatting?

 

[RexesOperator]

I now have the background colour set to white. I want the colour to change to yellow when there is no data. So I am trying

Field Value Is equal to "IsNull"

I tried dkinley's variation - that looks right, but what event would fire it?

 

[boblarson]

I now have the background colour set to white. I want the colour to change to yellow when there is no data. So I am trying

Field Value Is equal to "IsNull"

I tried dkinley's variation - that looks right, but what event would fire it?

Actually, your Field value should be

Is Null

with the space and without quotes

 

[RexesOperator]

That gives me a syntax error. With the quotes as in "Is Null" I don't get a syntax error or the expected result. I've attached a SnagIt image to show what I have.

I should this is on a continuous form.

 

[boblarson]

That gives me a syntax error. With the quotes as in "Is Null" I don't get a syntax error or the expected result. I've attached a SnagIt image to show what I have.

I should this is on a continuous form.

now that I think about it, I think you can't use it in Field Expression because you can't test something to be equal to null.

So, change to expression Is and then

IsNull([YourFieldNameInBrackets])

 

[RexesOperator]

Works like a charm - as always - you're the man!

 

[Minddumps]

So, change to expression Is and then

IsNull([YourFieldNameInBrackets])

Hi guys, I am trying to accomplish the same thing in my report: To highlight null valued fields. I used the advice you gave as quoted above, but the cells don't highlight unless the mouse is clicked on it.

Is there a different approach needed for Access 2007 to get the background to change upon load rather than onfocus?