# Need help with Yes/No field on a report

I have a report with a field named txtOnTime which gets filled with a Y or an N which comes from a query field that reads:

OnTime: IIf([MaxOfDateShpd]<=fnMax([Ack_Date],[ReschDate]),"Y","N")

This has been working fine until I add this to the end of it:

Or IIf([ReschDate]="","Y","N")

So I end up with:

OnTime: IIf([MaxOfDateShpd]<=fnMax([Ack_Date],[ReschDate]),"Y","N") Or IIf([ReschDate]="","Y","N")

Then all records end up with a -1 in them.

Here is the code in the fnMax module someone had helped me with some time ago.
Maybe it just has to be altered but I don't know how to do it.

``````Public Function fnMax(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) > myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMax = myVal

End Function
``````
###### Who is Participating?

Commented:
In that case your formula should be:
``````OnTime: IIf([ReschDate] Is Not Null Or [MaxOfDateShpd]>[Ack_Date], "N", "Y")
``````
0

Commented:
``````OnTime: IIf(([MaxOfDateShpd]<=fnMax([Ack_Date],[ReschDate])) OR ([ReschDate] Is Null),"Y","N")
``````
Ron
0

Author Commented:
Didn't work.  Now I'm getting all "Y"'s
0

Commented:
Are all the data types of your fields here set to TEXT or DATE?
0

Author Commented:
In this:

OnTime: IIf(([MaxOfDateShpd]<=fnMax([Ack_Date],[ReschDate])) OR ([ReschDate] Is Null),"Y","N")

All are date.
0

Commented:
So if there is no date given in ReschDate, you want it to be marked as OnTime, right? If that is the case, I don't see anything wrong with the formula I posted.  Could you provide some data for each of these fields where the results come up as "Y"?
0

Author Commented:
The data is very confidential but here is a screen shot (attached).

Using this example,

Anywhere Last Date Shipped > Ack Date it should be an "N" in the On Time column.

Anywhere Last Date Shipped <= Ack Date it should be an "Y" in the On Time column.

Anywhere the is a date in the ReschDate it should be an "N" in the On Time column

Does this help?
sample.jpg
0

Author Commented:
Thank you very much.

And mbizup... thank you for the information.

All is very much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.