Performing non bound field calculations in a form

[mondeoman]

I am fairly new to VB although have some experience with VBA in access. I have a form with a subform. This is based on a table called WorkDetails. The sub form is datasheet view. In the subform I have a start time (Start) field and a finish time (Finish) field. The field data typr for both is time(7) which give say a result of 10:35:00. What I want is to show the time elapsed between these two fields in hours and minutes (and a non visible field on total minutes). In access I would convert using Datediff with something like

"=DateDiff("n",[Start],[Finish])"

. I would then use something like this code to make the conversion and formating:

"=[txtTotalMinutes]\60 & Format([txtTotalMinutes] Mod 60,"\:00")"

. I have absolutely no idea how to go about this in a VB windows form can someone help please?

 

[Sorwen]

Dim StartTime As DateTime = '<put your field here>
Dim EndTime As DateTime = '<put your end field here>
Dim TimeDiff As TimeSpan

TimeDiff = EndTime.Subtract(StartTime)

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[mondeoman]

Thanks Sorwen. I think I have got myself a bit muddled. Given that this is part of a subfrm and in datasheet mode. Where do I put this? I have attached a gif image to try and explain what I am trying to do. Being new at this some simple feedthrough would be very welcome and helpful.

 

[Sorwen]

How are you getting your data into the DataGridView? I can't quite read what is at the bottom, but it looks like one is a BindingSource (maybe 2?) is that how? Assuming that is all correct is there a field you want to put the value in? Lets assume the field is called DateDiff.

        Dim StartTime As DateTime = '<put your field here>
        Dim EndTime As DateTime = '<put your end field here>
        Dim TimeDiff As TimeSpan

        TimeDiff = EndTime.Subtract(StartTime)

        dgv1.Rows(2).Cells(6).Value = TimeDiff.ToString 'sets 3nd row (rows start at 0) the 6th cell

        If dgv1.SelectedRows IsNot Nothing AndAlso dgv1.SelectedRows.Count > 0 Then
            dgv1.SelectedRows(0).Cells("DateDiff").Value = TimeDiff.ToString 'sets the selected row (the first if multiple selected) and the "DateDiff" cell
        End If

To ge the values you can use either of the same methods. If you are just wanting to do them all at once then you could do a for loop once the data is loaded.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[mondeoman]

Thanks again Sorwen. Actually what I have done whilst playing around is create a query and use it as a view in a datasheet on the form - see attched .gif file "qryWorkDetails.gif" in

http://www.box.net.

Do you think this is a legitimate way forward to carry out calculations or is there some underlying problem that I might come up against?

 

[Sorwen]

That should be fine.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[mondeoman]

Thanks - appreciate your help