Results 1 to 8 of 8
  1. #1
    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!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    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.

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



    but your code doesn't work
    mine will.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    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")

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    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?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    repalce the ' with "

    the select case allows you to set different text comments in 10 minute blocks
    its over the top / not needed if you just want the single comment
    if minute(ElapsedTIme) >=20 then FormatValue & ":- greater than 20 minutes"
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •