Calculated Field Based on Date/Time Fields (1 Viewer)

duncan17

Registered User
Joined
Jan 29, 2019
Messages
10
Hello, I have a table that has three fields. ActualNext(DateTime), CompletedDate(DateTime), OnTime(Calculated : either 1 for being on time or 0 for being late)

The expression that I am using in the OnTime field is :
IIF([CompletedDate]<=[ActualNext],"1","0")

This is working for some of the fields, like entering CompletedDate (1/1/2018) and ActualNext (1/15/2018) results in OnTime being 1.

However, when I have a CompletedDate (1/1/2019) and ActualNext (1/15/2019) it results as a 0.

I am trying to figure out why this is happening. It does not make sense that it would work correctly for one field but not another field. I would appreciate anyone's thoughts/feedback on what I might be doing wrong.

Thanks!
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,302
Hi. It's not obvious to me what the reason could be but one of the biggest reasons when calculations like that don't work is when you're looking at a number but the computer is seeing a text. For example, for numbers 2 is less than 10, but for texts, 2 is greater than 10 (because 2 is greater than 1 - the first digit in 10).
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top