IIF Statement returning #Error 1

[iuianj07]

Hello guys,

I created an IIF statement to determine status of a due date field. I have 2 columns "DueDate" and "TodaysDate"

And the status column should be: When the today’s date is greater than the due date, have the status say ‘Past Due'. If today’s date is two days earlier than the due date, have it say “Two Days until Past Due”

I created this IIF statement, but it returns #Error.

IIf([TodaysDate]>[DueDate],"Past Due",IIf([TodaysDate]=([DueDate]-2),"Two Days until Past Due","N/A"))

For some reason, when I wrote this first and ran the query, the column has "N/A" on it. but I accidentally closed the query without saving. and when I re-wrote the query it now gives this error message. it might be strange on my part.

Thank you

 

[dhookom]

Are these both date fields and does every record have date values in every field?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

No null value in the 2 date columns ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello dhookom and PHV,

Both columns have no Null values.

I got these columns from:

TodaysDate -- just a column that had Date() function. I tried to change the IIF statement to

IIf(Date()>[DueDate],"Past Due",IIf(Date()=([DueDate]-2),"Two Days until Past Due"," "))

but still gives the #Error message.

DueDate -- this came from a vba module

module:

Public Function dateAddNoWeekends(dtmDate As Variant, intDaysToAdd As Integer) As Variant
  Dim direction As Integer
  Dim intCount As Integer
If IsNumeric(intDaysToAdd) And IsDate(dtmDate) Then
    dateAddNoWeekends = dtmDate
    If intDaysToAdd < 0 Then
     direction = -1
    ElseIf intDaysToAdd > 0 Then
     direction = 1
    Else
     Exit Function
    End If
    Do
        dateAddNoWeekends = dateAddNoWeekends + 1 * (direction)
        If Not (Weekday(dateAddNoWeekends) = vbSaturday Or Weekday(dateAddNoWeekends) = vbSunday) Then
        intCount = intCount + 1
        End If
    Loop Until intCount = Abs(intDaysToAdd)
End If
End Function

then on my query:

DueDate: dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime])

I hope I was able to give suffice information.

Thank you

 

[PHV]

What about this ?
IIf(Date()>[DueDate],"Past Due",IIf(Date()=(dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime])-2),"Two Days until Past Due"," "))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hi PHV,

I tried your suggestion, and it gave this error message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Thanks for helping me out.

 

[iuianj07]

I also tried:

Status: IIf(Date()>(dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime])-2),"Past Due",IIf(Date()=(dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime])-2),"Two Days until Past Due"," "))

but it still gives the same error message as above.

 

[iuianj07]

this time, I tried creating a module to try and make this thing work, below is what I wrote...it's just a very simple one so let me know if I did it wrong (even if it should be simple)

Public Function getStatus(dteNow As Date, dteDue As Date) As Variant

If dteNow > dteDue Then
  getStatus = "Past Due"
  
ElseIf dteNow = (dteDue - 2) Then
  getStatus = "Two Days until Past Due"
Else
 getStatus = "N/A"
 End If
 
  
End Function

and on my query:

Status: getStatus([Date()],[DueDate])

when I run it, still gives the error message saying that the expression is typed incorrectly or too complex to be evaluated....

Any help is greatly appreciated...

 

[dhookom]

Often times you will get the too complex when you don't specify your data types or expect Access to convert for you.

It looks like your function returns a string so I would not use "As Variant". The same goes for a function that always returns a date. This should be "As Date".

I also do not reference a newly created alias in another expression in a query. If [DueDate] is an alias, don't use it to feed into another expression.

Also, why do you have [] around Date() in:

Status: getStatus([Date()],[DueDate])

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom,

sorry about the [] around Date(), don't know why I put that there.

I tried to change the function to "As Date" instead of "As Variant", and on my query, I changed it to:

Status: getStatus(Date(),dateAddNoWeekends(Maximum([MaxDate].[DateAssigned],[RushReqDate],[ResolvedIssueDate]),[TotalTATTime]))

but it still gets the same error message....

 

[dhookom]

If you pull out all the calculations/expressions, do all the date and numeric fields appear right aligned in the datasheet view? What is the Maximum() function?

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom...

All the date fields (DueDate, etc...) appear in the datasheet view. thanks

the Maximum() function is below:

Public Function Maximum(ParamArray MyArray()) As Variant

   Dim intLoop As Long

   Maximum = Null
   For intLoop = LBound(MyArray) To UBound(MyArray)

       If IsNull(MyArray(intLoop)) Then
           'do nothing
       ElseIf IsNull(Maximum) Then
           Maximum = MyArray(intLoop)
       ElseIf MyArray(intLoop) > Maximum Then
           Maximum = MyArray(intLoop)
       End If

   Next

End Function

thank you

 

[dhookom]

I expected the date fields to appear in the datasheet view. My question was do they appear left or right aligned.

Also, we have been suggesting you force the data type of functions and expressions. Maximum() returns a variant.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom,

DateAssigned is right aligned

RushReqDate is right aligned

RessolvedIssueDate is also right aligned

MaxDate is left aligned and DueDate is left aligned.

I also changed the Maximum() As Date...

thanks

 

[dhookom]

As I have stated, if a value appears left aligned, it is treated like a string, not date or numeric. I would make sure all values are converted to the data type they need to be.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello again dhookom,

I will make sure to double check on your suggestion above, into converting the data type as they need to be... as this really baffled me...

As of now, I was able to come up with a workaround, and it gives the results we need it to be... not sure though what's the difference with this one... but here is what I have:

Status: IIf(Date()>[DueDate],"Past Due",IIf(Date()=[DueDate],"Due Today",IIf((Date()+1)=[DueDate],"Due Tomorrow",IIf((Date()+2)=[DueDate],"Due in Two Days"," "))))

Thank you very much,, and I will try to give an update when I try to change data types again.. thank you

 

[iuianj07]

ok dhookom,

I just converted the functions to As Date() that's in the DueDate and Maximum functions... and now it returns what it needs to be... another small thing that I learned is about if the date/numeric fields are right or left aligned.

Just curious though why is it that when I wrote the above code (from most recent post) it is also working correctly, considering that I haven't changed the functions yet that time?

Anyway, thank you for your help again...

 

[dhookom]

Adding a numeric values such as +1 or +2 can force a data type to be numeric.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

oh okay... so that did the trick... thanks again Duane

 

[iuianj07]

hello again guys,

okay follow up question,, a different query but should be with the same output...

Why is it that whenever TotalTATTime = 0, the DueDate appears as 12/30/1899...?

I verified that all dates are right aligned...

any help is greatly appreciated.

Thanks