conditional format for deleted data

[vickiwells]

I have a date field I'm using conditional formatting in. Some of the conditions depend on whether there's any data in another field. It was working fine using ISBlank, until I deleted data from that field in one of the records. Now the formatting doesn't work for that record. What's the "status" of a field that had data in it but doesn't anymore? I've tried IsBlank, IsEmpty, IsNull, IsMissing and "", but can't find a combination that works.

 

[Minty]

The most common is either Null or "" (Zero length string or ZLS) and you have to test for both. One of the more common ways is
Len(YourField) & "" >0
Which adds a ZLS to your field and see's how long it is.

 

[sneuberg]

I've tried IsBlank, IsEmpty, IsNull, IsMissing and "", but can't find a combination that works.

I don't thing Access has a IsBlank function. Are you talking about Access or Excel?

 

[vickiwells]

I tried this and it didn't work. Did I do it right?

DateAdd("m",1,Date()) And Len([CONCEPTUAL_LOCATION_STUDY_CD] & "">0)

 

[vickiwells]

You're right, I guess I know why that one didn't work!

 

[Minty]

Excel conditional formatting completely baffles me, so I will dip out at this point.
Forget I was here
What's my name?

 

[sneuberg]

I tried this and it didn't work. Did I do it right?

DateAdd("m",1,Date()) And Len([CONCEPTUAL_LOCATION_STUDY_CD] & "">0)

What are you trying to do by adding DateAdd("m",1,Date()) to this?. That returns 6/12/2016 which is not a boolean value. Your expression should evaluate to true or false.

Also is [CONCEPTUAL_LOCATION_STUDY_CD] in the record source?

 

[vickiwells]

I need it to look for a date in the first field that's within a month of today's date, but color it only if the second field is blank. Yes, all the fields are in the same table.

 

[sneuberg]

Ok this could be interesting. How do you want to "define within a month"? Let's say the "first field" is named [FirstField] how about

abs(datediff("d",Date(),[FirstField]))<30

Which would be true if [FirstField] was within 30 days of today's dates. Or do you want something else?

If that's what you want then the complete expression would be


abs(datediff("d",Date(),[FirstField]))<30 And Len([CONCEPTUAL_LOCATION_STUDY_CD] & "">0)

 

[vickiwells]

What I need is for the scheduled date to turn yellow if it's a month or less from today. Then when any date is actually entered in the completed date field I need the yellow to go away from the scheduled field.

The expression below works if the completed date hasn't had anything deleted, but not if a date has been removed from the completed column.

DateAdd("m",1,Date()) And Len([CONCEPTUAL_LOCATION_STUDY_CD]) & ""=0

Hope this makes sense. I'm going to try to attach a picture of what the data should look like.

Thanks for all your help!:banghead:

 

[sneuberg]

Maybe you have the combo box in the conditional formatting rules dialog set to Field Value Is. If you are using more than one field in your expression then you need to use Expression Is (see attached). The expression must be a boolean expression and so all of the fields need to be in it. Below is the expression I think you want if you change [DueDate] to the actually field in your record source.


[DueDate] < DateAdd("m",1,Date()) And Len([CONCEPTUAL_LOCATION_STUDY_CD]) & ""=0

 

[vickiwells]

I think we've got it! You were right, I was using the "Value Is" instead of "Expression Is. It still wasn't working, so I tried using Nz, and it seems to be working.
Thanks again for all the help!

[CONCEPTUAL_LOCATION_STUDY_SD]<DateAdd("m",1,Date()) And Nz([CONCEPTUAL_LOCATION_STUDY_CD])=0

 

[vickiwells]

How do I mark this as solved?

 

[sneuberg]

It bothers me that Len([CONCEPTUAL_LOCATION_STUDY_CD]) & ""=0 didn't work.. Since Nz([CONCEPTUAL_LOCATION_STUDY_CD])=0 works it should have.

To mark as solved go to the top of the thread. Click on Thread Tools. You will find it in the drop down.