Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

SQL to Code: How to write it.

I have a form in which are input two dates which I would like to display in a text box by using the AfterUpdate Event of the second date field. The SQL for the query that subtracts the two dates is:

SELECT CInt(([EndDateTime]-[StartDateTime])*1440) Mod 60 AS Minutes, CInt(([EndDateTime]-[StartDateTime])*24) Mod 24 AS Hours, Format([Hours],"0") &  " hours, " & Format([Minutes],"0") & " minutes" AS Elapsed
FROM tblTrades;

How would I write that in code format?

Thanks.
RWW
0
PstWood
Asked:
PstWood
  • 3
1 Solution
 
harfangCommented:
Hello PstWood

A translation to VB could look like this:


Private Sub txtEndDateTime_Click()

    Dim lngMinutes As Long
    Dim lngHours As Long

    lngMinutes = CInt((txtEndDateTime - txtStartDateTime) * 1440) Mod 60
    lngHours = CInt((txtEndDateTime - StartDateTime) * 24) Mod 24
    txtElapsed = Format(lngHours, "0") & " hours, " & Format(lngMinutes, "0") & " minutes"

End Sub


You can also use the exact same syntax you had in the query for textboxes on your form. For example, start with a textbox txtMinutes, and use:

    = CInt(([EndDateTime]-[StartDateTime])*1440) Mod 60

And continue with txtHours and txtElapsed. Then hide those you don't want (or, of course, incorporate the definitions of Minutes and Hours into the last expression, making one very long but valid expression...


(°v°)
0
 
harfangCommented:
I just noticed the Mod 24. If you do want a wrap at 24 hours, you can use simply this:

    = Format(EndDateTime-StartDateTime, 'h" hours, "n" minutes"')

Or, in VB:

    txtElapsed = Format(EndDateTime - StartDateTime, "h"" hours, ""n"" minutes""")

I was under the impression you needed the function to *allow* results over 24 hours. Incidentally, I think I would do it like this, though:

Private Sub txtEndDateTime_Click()

    Dim lngMinutes As Long

    lngMinutes = CLng((txtEndDateTime - txtStartDateTime) * 1440)
    txtElapsed = (lngMinutes \ 60) & " hours, " & (lngMinutes Mod 60) & " minutes"

End Sub

Anyway, good luck!
(°v°)
0
 
PstWoodAuthor Commented:
Yep. That worked. I made the following changes to include Me. for the text boxes being used.

Private Sub EndDateTime_AfterUpdate()

    Dim lngMinutes As Long

    lngMinutes = CLng((Me.EndDateTime - Me.StartDateTime) * 1440)
    Me.Duration = (lngMinutes \ 60) & " hours, " & (lngMinutes Mod 60) & " minutes"

End Sub

Thanks again.
RWW
0
 
harfangCommented:
Glad it worked, and success with your application!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now