# Thread: Running comparison on Datediff with modified format.

1. Registered User
Join Date
Nov 2012
Posts
79

## Unanswered: Running comparison on Datediff with modified format.

Good evening and salutations!

I have a form that runs time calculations for me which works wonderfully. I would like to be able to run comparisons on those times.

The code that runs the times is as follows:

Code:
`txtbox = (dateDiff("n", [DispTimeCalc], [CathTimeCalc])) \ 60 & Format*((DateDiff("n", [DispTimeCalc], [CathTiemCalc])) mod 60, "\:00")`
So it will return the difference between two times as hh:mm.

I would like to be able to compare the minutes. For instance:

If :mm >20 then txtbox = "suckaroony"

Thanks!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
consider taking advantage of the way that Access (like all db's) stores datetime values as a number. In Access's world its a decimal number. The integer part is the number of days since 21/12/1899, the decimal part is the proportion of the day. so:-
.5 = 12:00:00
.75 = 18:00:00
.5001157400741 (or 10/60/60/24) is 12:00:10

so subtract your dates and steo into another datetime variable

Dim ElapsedTime as DateTime
Dim FormatValue as string
ElapsedTime = DispTime Calc = CathTimeCalc
FormatValue=""
if ElapsedTime >1 then
FormatValue = int(ElapsedTime) & ' days, '
endif
FormatValue = FormatValue & format (ElapsedTime,'hh:mm')
select case minute(ElapsedTIme)
case 0 to 9: FormatValue = FormatValue & ':- range1'
case 10 to 19: FormatValue = FormatValue & ':- range2'
case 20 to 29: FormatValue = FormatValue & ':- range3'
case 30 to 39: FormatValue = FormatValue & ':- range4'
case 40 to 49: FormatValue = FormatValue & ':- range5'
case 50 to 59: FormatValue = FormatValue & ':- range6'
case else: FormatValue = FormatValue & ':- out of range' 'shouldn't happen
end select
txtbox.value = FormatValue

3. Registered User
Join Date
Nov 2012
Posts
79
Huh? You lost me dude!

My output is hh:mm. I just need to know if mm is > 20. All the time adding/subtracting is done.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Huh? You lost me dude!
well that's fair enough..

but your code doesn't work
mine will.....

5. Registered User
Join Date
Nov 2012
Posts
79
I'm confused. The code works fine. It prints the correct difference even with a 24 hour overlap.

I didn't post the whole thing, didn't think it relevant.

Code:
```DispTimeCalc = Me.Cardiac_Date & " " & Me.Cardiac_TDisp
CathTimeCalc = IIf([Cardiac_TTx] < [Cardiac_TDisp], [Cardiac_Date] + 1 & " " & [Cardiac_TTx], [Cardiac_Date] & " " & [Cardiac_TTx])

Me.Cardiac_TTot = (DateDiff("n", [DispTimeCalc], [CathTimeCalc])) \ 60 & Format((DateDiff("n", [DispTimeCalc], [CathTimeCalc])) Mod 60, "\:00")```

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
no
the problem was that the code you posted had a typo
Code:
`txtbox = (dateDiff("n", [DispTimeCalc], [CathTimeCalc])) \ 60 & Format*((DateDiff("n", [DispTimeCalc], [CathTiemCalc])) mod 60, "\:00")`
I'd suggest that your approach is also clunkier

7. Registered User
Join Date
Nov 2012
Posts
79
Ah. I'll agree with you on clunky. I'm an amateur at VBA. I started playing with Access 3 months ago and the last time I had any programming experiences was just before HTML 4 came out. It's better than it was as I discover better ways of doing things.

As for you code.
Code:
`Formatvalue = Formatvalue & format(elapsedtime,'hh:mm')`
VBA editor doesn't like the single quotes. As for the rest of it. I recognize some parts of it but whats all the range stuff?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692