Issue with DateDiff (1 Viewer)

[Pis7ftw]

I'm having some really weird returns that don't make any sense whatsoever and don't even add up correctly. I've had no luck reading through pages and pages of google searches looking for the solution.

I have two text fields on my form.. txtTime1 and txtTime2. Both are set for Short Time format, I have an unbound field, txtOutput from which I want the difference displayed. All three fields on the corresponding table are date/time fields. I want the output displayed as hh:mm in txtOutput.

I've tried:

Dim Boop As Integer
Dim Beep As Integer

Boop = DateDiff("h", txtTime1, txtTime2)
Beep = DateDiff("n", txtTime1, txtTime2) Mod 60

Me.txtOutput = Boop & ":" & Beep

Which doesn't even add up...at all or depending on how I changed field types and field formats, I get either runtime errors saying values aren't right or just strange numbers in general.

I've tried all sorts of other combinations that I can't remember off hand.

I don't know if my format is wrong, my field types are wrong, the code is wrong, the calculations are wrong. No idea.

Any ideas?

 

[Pis7ftw]

Yeah..I tried something else just now. DateDiff between 00:23 and 01:45...it gave me 12/30/1899. :banghead:

 

[Pis7ftw]

Bump........

 

[John Big Booty]

Try;

Me.txtOutput =Int(DateDiff("n",[txtTime1],[txtTime2])/60) & ":" & DateDiff("n",[txtTime1],[txtTime2])-Int(DateDiff("n",[txtTime1],[txtTime2])/60)*60

Also see this link for the correct syntax for the Mod Operator.

 

[Accessensible]

As u r using text fields for your dates u'll have to format these to be able to use datediff.
something like this

"#" & Format([txttime1], "mm-dd-yy") & "#"

 

[Pis7ftw]

Try;

Me.txtOutput =Int(DateDiff("n",[txtTime1],[txtTime2])/60) & ":" & DateDiff("n",[txtTime1],[txtTime2])-Int(DateDiff("n",[txtTime1],[txtTime2])/60)*60

Also see this link for the correct syntax for the Mod Operator.

That's what I was looking for. Thanks for the info. Now it displays in hh:mm format which is great. But it saves in the table as hh:mm:ss AM/PM.

I would like it to save in the table as hh:mm in 24 hour format.

 

[PeterF]

If it's a date/time field in the table it's stored as a number where 1 equals 24 hours or a day.
The way it's displayed depends on your regional setting or (if defined) the format defined in the table.

 

[John Big Booty]

That's what I was looking for. Thanks for the info. Now it displays in hh:mm format which is great. But it saves in the table as hh:mm:ss AM/PM.

I would like it to save in the table as hh:mm in 24 hour format.

Given that you have calculated this value in this instance, why do you need to store it Storing calculated values is in most cases considered bad practice. If one of the underlying values is changed this will not reflected in your stored value. Now that you know how it is done simply calculate the value whenever you wish to display it.

 

[Pis7ftw]

Understood and this is a strange circumstance where I would like to. Essentially the Form tracks the times during a flight mission.

Dispatched: xx:xx
On Scene: xx:Xx
Leave scene: xx:xx
At destination: xx:xx
Available: xx:xx

with a bunch of other times in chronological order between those. This form calculates the time between certain fields to ensure that we are meeting a standard. I want those calculated times displayed in disabled text field for reference and also logged in the table. Then if a time is out of compliance such as greater than 20 minutes then displayed in red, less than 20 green etc. If all times are in compliance then the entire audit is compliant, if not then it's non-compliant which is also stored.

There are a lot of times and these calculated times pick out the important ones.

No calculations take place until a "Verify" button is pressed which ensures no fields are null and then the calculations are displayed and subsequently a submit button which saves the record. If things look weird then times can be changed which disables the submit button until the verify button is clicked again.

This one is pretty heavy compared to all of the other ones which are much simpler and only have a calculated field that says "Yes" or "No" based on certain choices. Needless to say with my limited Access and VB experience it's proving to be the most difficult. I haven't had to deal with any major number calculations up to this point and don't foresee having to do it again.