Conditional Formatting for Blank Date Field Not Working (1 Viewer)

[Tophan]

Hi,

I have a report which is based on a query that includes the following expression named "PreInstallationInspectionDate" (I know it's a long name)

DLookUp("[PreInspectionDate]","tblWorkOrder","CandidateID=" & [CandidateID])

In the report, I would like the PreInstallationInspectionDate field highlighted if the field is blank. I've tried using IsNull([PreInstallationInspectionDate]), Nz([PreInstallationInspectionDate]) and both IsNull and Nz with the Dlookup formula used in the query but nothing is working.

Any suggestions?

 

[theDBguy]

Is PreInspectionDate a Date/Time field? Does it have a Default Value?

 

[Tophan]

Is PreInspectionDate a Date/Time field? Does it have a Default Value?

It is a Date/Time field with no default value

 

[theDBguy]

If you use Nz() in your expression, what happens?

Nz(DLookup(...), "")

Then, in your Conditional Formatting, try to use:

Value Is: ""

 

[SHANEMAC51]

I have a report which is based on a query that includes the following expression named "PreInstallationInspectionDate" (I know it's a long name)

I prefer pre_installation_inspection_date-much more readable and without spaces, otherwise square brackets will be needed

 

[Gasman]

Why not check for Len() > 0 ?

 

[Tophan]

If you use Nz() in your expression, what happens?

Nz(DLookup(...), "")

Then, in your Conditional Formatting, try to use:

Value Is: ""

Not getting the conditional formatting to work with the Nz or with the suggestion of Nz(Dlookup(...),"")

 

[Tophan]

Why not check for Len() > 0 ?

I am not familiar with this one. Would I type Len([PreInstallationInspectionDate])>0?

 

[Tophan]

I prefer pre_installation_inspection_date-much more readable and without spaces, otherwise square brackets will be needed

Thanks. I couldn't find a shorter description for that field. I will try your suggestion.

 

[Gasman]

Thanks. I couldn't find a shorter description for that field. I will try your suggestion.

You do know that fields have captions?, that can describe a field better on forms/reports?
If that was my DB the field would be called something along the lines of PreInstallInspDate or even shorter. and the caption Pre Installation Inspect Date.

 

[Gasman]

I am not familiar with this one. Would I type Len([PreInstallationInspectionDate])>0?

Yes, or whatever the control name is.
Most people rename the control slightly to differentiate between field and control, like txtPreInstallationInspectionDate, making it even longer.

 

[Tophan]

Yes, or whatever the control name is.
Most people rename the control slightly to differentiate between field and control, like txtPreInstallationInspectionDate, making it even longer.

That didn't work either

 

[theDBguy]

If you use Nz() in your expression, what happens?


Not getting the conditional formatting to work with the Nz or with the suggestion of Nz(Dlookup(...),"")

Hi. Can you post a sample db with test data to demonstrate the problem?

 

[theDBguy]

Is this what you mean?

Edit: File attachment deleted per OP's request.

 

[Pat Hartman]

Value Is: ""

"" is NOT the same as Null. "" is a Zero Length STRING and isn't even valid for dates which are numeric.

I used my own db for this sample so the date name is different from yours.

 

[Tophan]

Yes! That's what I need to highlight! So it's the order of the expression - [PreInstallationInspectionDate] Is Null

As always, thank you so much for your help

 

[Tophan]

I'm going to delete the DB from the forum. I really appreciate everyone's help on this.

 

[theDBguy]

Yes! That's what I need to highlight! So it's the order of the expression - [PreInstallationInspectionDate] Is Null

As always, thank you so much for your help

That's just one way to do it. I figured if I could avoid using a function, then why not?

Good luck with your project.

 

[theDBguy]

I'm going to delete the DB from the forum. I really appreciate everyone's help on this.

Would you like me to delete the db in my post as well?

 

[Tophan]

Would you like me to delete the db in my post as well?

Yes thank you. Please delete.