Guys kindly check this code of mine. Supposed to be Text3.text once inserted into Table1 should give the total work hours but instead, it gives a date, not time.

Private Sub Command1_Click()
Dim Str1$, Str2$, Str3$, Str4$
Text2.Text = DateTime.DateDiff("n", Text1.Text, Label1.Text)

Str1 = Text2.Text \ 60
Str2 = Text2.Text Mod 60
    Text3.Text = Str1 & " hours & " & Str2 & " minutes"
    Adodc1.RecordSource = "Insert into Table1"
    Adodc1.Recordset.Fields("HoursTotal") = Val(Text3.Text)

    If Val(Text2.Text) > 480 Then
        Label2.Caption = Text2.Text - 480
        Str3 = Label2.Caption \ 60
        Str4 = Label2.Caption Mod 60
        Text4.Text = Str3 & " hours & " & Str4 & " minutes"
End If
End Sub

There are so many questions about your code...where to start? Okay, so, in no particular order:

What is the datatype of the HoursTotal field in Table1? If it's a datetime and you want to manipulate it in your VB code, then you'll probably want to look at some of the FORMAT function capabilities.

If you want to use system/built-in functions to format it on the DBMS side, then it matters what DBMS you are using. Syntax differs.

When you use VAL against Text3.Text, it will simply give the value of the first number it finds. So, your formatting on line 7 shows "20 hours & 50 minutes" or something, so VAL evaluates to "20". This may be what you want, I'm not sure.

One other little thing: in line 3, you should probably be referencing Label1.Caption, not Label1.Text (like you do on line 12, 13 and 14).

So, last question...what do you want stored in the database? Just a count of hours, irrespective of minutes? Or (using the above example) something like "20:50"? What do you want displayed on your form when you redisplay it?

Okay, so your HoursTotal column is indeed defined as a datetime. Therefore, when you just move an integer into it, you get a formatted date that counts with the integer the number of days from 1/1/1900. Note that in actual MSAccess storage terms, the "date" part of the datetime is the integer portion, the "time" portion is the decimal portion.

To fix this you might consider a few different options:
1. Change the HoursWorked to a number (either decimal or integer, depending on if you want to keep hours and minutes, or just hours).
2. Change the HoursTotal column to a text datatype, and just format your string as "20:50" or whatever you want.
3. Don't bother to store HoursTotal at all, and just write some code to use DateDiff on TimeIn and TimeOut to calculate it for display purposes whenever you need it.
4. Don't bother to store HoursTotal, just use some of the interesting MSAccess built-in functions to derive the column, and use that SQL statement as your ADODC1.Recordsource.

Oh, and nevermind about the "Label1.Text" vs. "Label1.Caption"...apparently Label1 is a textbox, not a label. A little confusing, but not the end of the world.

Hope these ideas were helpful. Good luck!

Edited 4 Years Ago by BitBlt

This article has been dead for over six months. Start a new discussion instead.