Advanced Conditional Formating

[Djblois]

I am trying to use this formula in conditional formatting and it says that syntax is wrong:

And(IsNull([tbArrTime]),[tbSchTime]<Date)

What I want the code to do is check if [tbArrTime] is null and [tbSchTime] is less than the Current time. Meaning the Appointment is late.

 

[Access Hero]

Not sure exactly what you're trying to do, but I believe you want:

And
[tbArrTime] is null
And
[tbSchTime] < Date

With the assumption that "Date" refers to the VBA function Date() (aka the current system date).

 

[Djblois]

Yours doesn't work either. I may be wrong but your formula does not even look right because I use this formula in another conditional format and it works:

IsNull([tbArrTime])

and that is closer to my syntax. I don't know - maybe there are two forms of syntax you can use in conditional formatting.

 

[Access Hero]

Sorry, I wasn't paying very close attention to exactly what you're doing. Does this work:

And
IsNull([tbArrTime])
And
[tbSchTime] < Date

Please note that IsNull() does not use the syntax in your original sample (it only takes 1 argument).

 

[Djblois]

no still saying wrong syntax.

 

[Access Hero]

Ok. In your original post, you show the word "And" at the beginning of your code. What comes before that?

 

[missinglinq]

The syntax would be

IsNull([tbArrTime]) And [tbSchTime]<Date()

but you say "[tbSchTime] is less than the Current time" so you need to use Now() rathet than Date().

IsNull([tbArrTime]) And [tbSchTime]<Now()

 

[Djblois]

would now take today's date into account also?

 

[Access Hero]

Yes. Now returns the date and time. Date returns today's date (I think at midnight).

 

[missinglinq]

As Hero says, Date() will return the current date with a time of midnight, which is why I didn't think it would work in this instance; chances are any appointment is going to show as 'late' when compared to midnight of the same day!