Calculated fields, hours tracking dates (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 15:52
Joined
Sep 17, 2019
Messages
159
Hello,

I Have 4 fields with dates and times. Time field is in Military time. Fields: US_date, US_time, CXR_date, CXR_time. I want to create 3 calculated fields using those other fields to determine how many hours apart each of the date, times are. Calculated fields: 24hr_before, 24hr_after, more_24hr. I want the calculated fields to show up as a 1 if it's TRUE or 0 if false.

If the cxr_date is within 24 hours before us_date, then 24hr_before field would be true and should show up with a "1", the other 2 fields will show up as "0"

Would I have to concate (combine)the dates and times together first in another field before I could do this calculation? Any help would be appreciated. I'm a newbie with access.
 

June7

AWF VIP
Local time
Today, 12:52
Joined
Mar 9, 2014
Messages
5,423
If these are date/time type fields, data is actually stored as a long number. Add the date and time fields to get a complete date/time value. Use DateDiff function to calculate hours difference.

HrDiff: DateDiff("H", US_date + US_time, CXR_date + CXR_time)
 

plog

Banishment Pending
Local time
Today, 15:52
Joined
May 11, 2011
Messages
11,611
From first blush your table is set up incorrectly and what you want to do isn't in the right place.

In Access there's a field type called Date/Time, it can hold both Date and time information. So, instead of storing 9/17/2019 in one field and 18:30 in another all of that data can go into just one field. Which makes it a lot easier to work with when you want to do calculations. Why do you have your date/time fields set up as they are?

Second, calculated fields should not be in tables. When you want to calculate data you should do it in a query and then reference the query when you want that calculated value. When you have your date/time values stored properly this can be achieved easily using the DateDiff function (https://www.techonthenet.com/access/functions/date/datediff.php) built into Access.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:52
Joined
May 7, 2009
Messages
19,169
you can create a query to show the calculated fields:
Code:
select US_date, US_time, CXR_date, CXR_time, 
  iif(datediff("h", US_date + US_time,  CXR_date + CXR_time) < 24, 1, 0) as 24hr_before, 
  iif(datediff("h", US_date + US_time,  CXR_date + CXR_time) >= 24 and 
      datediff("h", US_date + US_time,  CXR_date + CXR_time) < 48, 1, 0) as 24hr_after, 
  iif(datediff("h", US_date + US_time,  CXR_date + CXR_time) >=48, 1, 0) as 24hr_more 
from yourTableName;
 

Users who are viewing this thread

Top Bottom