Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-30-2019, 02:38 PM   #1
duncan17
Newly Registered User
 
Join Date: Jan 2019
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
duncan17 is on a distinguished road
Smile Calculated Field Based on Date/Time Fields

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!

duncan17 is offline   Reply With Quote
Old 01-30-2019, 02:42 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,593
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Calculated Field Based on Date/Time Fields

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).
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Tags
access , iif , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Field calculated output based on date range CK_One Tables 16 02-22-2017 02:21 AM
Calculated field based on date range brichard Queries 3 09-05-2012 06:28 AM
overcoming blank date fields when used in calculated field in a query grapevine Queries 8 02-25-2009 02:09 AM
Updating other fields based on another calculated field Yarra518 Tables 2 01-29-2004 12:25 PM




All times are GMT -8. The time now is 08:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World