Conditional formatting in a report based on 2 criteria including dates

[Angelina725]

I created a database that tracks the process of building a complex document with several approval steps. On one of the reports, I would like to use conditional formatting based on the following:

If [Approval Received] is null and [Approval Sent to Supervisor] > 5 business days prior to current date, color [Approval Received] text box red

I have attempted using the NetworkDays formula but I can't seem to translate it into Access language.

Any help is appreciated.

Thank you!

[beacon]

Hi Angelina,

Welcome to Bytes!

Do you need holidays included in your business days?

Personally, and assuming that your report is based on a query, I would put the calculation in the query and then add the conditional formatting to the report. This has always been quite a bit more efficient for me since the end user is typically the person that views the reports (and they don't like lag time that might take place if you have a bunch of calculations in your report).

[Angelina725]

Yes. I already have a holiday table created and I have the code written for a custom function but every time I try to use my function in a query it returns an undefined function error. Code is below:

Public Function NetWorkDays(dte Start As Date, dteEnd As Date) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkDays = 0

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurr Date, vbMonday) < 6 Then
If IsNull(DLookup( "[dtObservedDate]", "tbl_Holida ys", "[dtObservedDate] = #" & dteCurrDate & "#")) Then
NetWorkDays = NetWorkDays + 1
End If
End If
Next i

End Function


Any help is greatly appreciated!

[colintis]

Angelina,

Can you also highlight the line that points the error? and it would be better if you can post the query's SQL codes as well.

Also, use CODE TAG (the # button) for pasting the codes.

[Angelina725]

Thank you both for your help but Google finally prevailed! The problem was the name of my function and the name of my VBA module were the same. Once I renamed my VBA module the function worked.