Calculated Date Field in Query

[GinaS]

Hello all, I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))

This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA

 

[plog]

Sounds like you aren't capturing the data you need. Instead of this convoluted hack, you need to capture the Date portion as well. When you do that, you can do your simple DateDiff calculation and it will calculated correctly on its own.

 

[GinaS]

Thanks plog - I may have a problem doing that because the form is designed to capture 13 different times from when the patient arrives to the hospital and then as they transition through the different departments like ER, LAB....etc. When designing the form the users stated they only need the date displayed once and then want the other fields to capture times only.

 

[plog]

This might be the first instance of this happening ever in software, but your user was wrong. Since the date is a relevant piece of information, you must capture it.

Now, displaying and capturing are two different beasts. You can still have that form display the time in all those inputs, but since you need to work with the date, you need to capture it.

 

[GinaS]

Thank you plog - it appears I may have some work ahead of me (capturing date in all the fields). I'm attaching a strip down version of my database to show what I have with the hopes of not having to redefine all the fields. In this version of my database I only included 2 queries. qryRptED shows the calculated fields AT_EKG and AT_ST (last 2 fields) - both of these are examples of patients that arrived before midnight but didn't get service until after midnight so you will see AT_EKG = 1410 and AT_ST = 1409. qryRptED2 shows the fix for these fields and calculations now show 30 and 31 minutes (rightfully so) but notice all of the negative fields now have been changed. Hopefully this will help and maybe I can see an example of what I should do. Thanks in advance for your help. By the way - ignore the informational box that comes up when you first click on the queries, this is not a issue I just made a real quick stripped down version without my other forms and tables.

 

[plog]

What you should do is get the correct Date value in all those time fields, then all your DateDiff functions will work properly without having to determine if they are valid negative numbers or if they need to be rolled to the next date.

The good news is you have the date in there ('Date' is a poor choice for a field name because its a reserved word and will cause issues when writing code). So what you should do is make a copy of this database and work in that copy to get your data how you need it.

Step 1--alter all the Short Time formatted fields to a General date format.

Step 2--update all those fields by making a new Date/Time value comprised of the 'Date' field value and the current time inside each field.

Step 3--Add 1 day to all those values that occur in the next day.

Step 4--validate your data. Make sure you've done everything correctly.

Step 5 -- copy and replace the live table with the one you modified to hold the date.

 

[GinaS]

Ok - Thank you plog, I will work on those changes and see what happens.

 

[GinaS]

plog - WORKED like a charm!! Thank you. However, I do have one last question. With this calculation I can't report negative times. For instance, what is suppose to be -4 is now showing as 23:56, -12 show as 23:48. Is there a way to fix this?

 

[plog]

I don't understand, if you are using the DateDiff function it won't return a date value, it will return an integer.

 

[GinaS]

I didn't use DateDiff - I wasn't sure how to use it with the +1 day. I used Format([arrived]-1-[1st_ekg], "Short Time"). I attached my database with the new results.

 

[plog]

Your going to have to explain what you are trying to do. When you format things as times, they come out as times. If it equates to 14 minutes before midnight, that time is going to be 23:46. Again, I don't understand.

 

[GinaS]

I'm dealing with patients that may start off with treatment prior to arriving at the hospital which result in a negative split and this is expected in my organization to make sure proper procedures are being followed:
1 - patient receives EKG (1ST_EKG) in ambulance at 10:00 pm
2 - ambulance personal send transmission of EKG to hospital 10:05 pm
3 - hospital receives report, speaks with ER Doctor, ER Doctor responds with activate STEMI alert 10:10 pm
4 - patient arrives at hospital 10:20 pm

This will result in the first field AT_EKG (time difference between when patient arrived and 1st EKG received in ambulance) -10 minutes
The 2nd field AT_ST (time difference between when patient arrived and stemi activation) -20 minutes

 

[GinaS]

The problem with the negative time wasn't a problem until I fixed the midnight issue. So one fix resulted in a different problem. I think my original post was only concerned with fixing the midnight issue.

 

[plog]

This will result in the first field AT_EKG (time difference between when patient arrived and 1st EKG received in ambulance) -10 minutes

To get the time difference, you use the DateDif function. Why are you formatting as time, if you want an integer result?

AT_EKG: DateDiff("n", [ArrivalTime], [1st_EKG]) & " Minutes"
AT_ST: DateDiff("n", [ArrivalTime], [STEMI]) & " Minutes"

DateDiff documentation: http://www.techonthenet.com/access/functions/date/datediff.php

 

[GinaS]

Thanks plog but this puts me back to the original problem of havine -1410 because one of the fields is past midnight. I'm thinking I might need a conditional 'IF' statement somewhere....your thoughts?

 

[plog]

Nope, if you changed your data to how it should be structured (with dates) then it doesn't.

If:

ArrivalTime = 9/11/2014 12:04 AM
1st_EKG = 9/10/2014 11:58 PM

Then:

AT_EKG: DateDiff("n", [ArrivalTime], [1st_EKG]) & " Minutes"

Will produce:

-6 Minutes


Looking at your database, you didn't do what I suggested (incorporating Dates with your times).

 

[GinaS]

Oh sorry, I must of missed a step, I thought once I changed all the fields to Standard Date format that it would automatically incorporate the date. Can you tell me what I'm suppose to do to include the date portion?

 

[plog]

See Post #6:

What you should do is get the correct Date value in all those time fields, then all your DateDiff functions will work properly without having to determine if they are valid negative numbers or if they need to be rolled to the next date.

The good news is you have the date in there ('Date' is a poor choice for a field name because its a reserved word and will cause issues when writing code). So what you should do is make a copy of this database and work in that copy to get your data how you need it.

Step 1--alter all the Short Time formatted fields to a General date format.

Step 2--update all those fields by making a new Date/Time value comprised of the 'Date' field value and the current time inside each field.

Step 3--Add 1 day to all those values that occur in the next day.

Step 4--validate your data. Make sure you've done everything correctly.

Step 5 -- copy and replace the live table with the one you modified to hold the date.

 

[GinaS]

thank you plog, I finally got it, it's working correctly. I appreciate your help