Time Calculation - Run-time error 13 - Type mismatch (1 Viewer)

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
894
I have 2 columns in my server table i.e. OTFrm(time(7)) and OTTill(time(7)) that placed on my MS Access subform.

The time I have entered is as follows

15:20:00.0000000
16:22:00.0000000

How can I calculate the difference between these two times in OTTotHrs?

I tired with this
Dim X
X = DateDiff("n", OTFrm, OTTill) \ 60
OTTotHrs=X
But it produces Run-time error 13 - Type mismatch

Anyidea?
 

Ranman256

Well-known member
Local time
Today, 08:59
Joined
Apr 9, 2015
Messages
4,339
As is, this is not time. Remove the decimal value.

VTime=left(field,instr(field,".")-1)
Then you can do date math on vTime.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:59
Joined
May 7, 2009
Messages
19,169
you can also create a public function to convert the string time to real time:
Code:
Public Function cnvToAccessTime(ByVal p As Variant) As Date
    Dim i As Integer
    i = InStr(p, ".")
    If i > 0 Then
       p = Left$(p, i - 1)
    End If
    cnvToAccessTime = CDate(p)
End Function
then on your computation:
Code:
Dim X
X = DateDiff("n", cnvToAccessTime(OTFrm), cnvToAccessTime(OTTill)) \ 60
OTTotHrs=X
 

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
894
you can also create a public function to convert the string time to real time:
Code:
Public Function cnvToAccessTime(ByVal p As Variant) As Date
    Dim i As Integer
    i = InStr(p, ".")
    If i > 0 Then
       p = Left$(p, i - 1)
    End If
    cnvToAccessTime = CDate(p)
End Function
then on your computation:
Code:
Dim X
X = DateDiff("n", cnvToAccessTime(OTFrm), cnvToAccessTime(OTTill)) \ 60
OTTotHrs=X
Fantastic idea......Thanks Arnel....
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 28, 2001
Messages
26,996
Not sure of where you got those times, but the problem lies in the data formatting code built-in to Access and its libraries. It is true that a date/time variable can hold fractions of a second. In fact, dates in the current century could probably do pretty well at the microsecond level. But the standard Access date/time formatting code only goes as far as seconds for input or output.

To retain fractional seconds properly would require you to write a user-defined function for interconverting dates and times to/from strings showing fractional seconds. It is doable, just not pretty. If the fractions are always ".000000" then it seems rather pointless to even keep them. So the solution of truncating the string at that decimal point is probably for the best.

In case anyone was wondering, certain networking logs that you download from other sources will contain fractional times in UTC format but to make the reports work right, you need to diddle the date/time-with-fraction carefully. I had that misfortune. I can't post the code because it was for a U.S.Navy project and I don't own the code. And I didn't keep copies of it. Part of an NDA.
 

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
894
Code:
If the fractions are always ".000000" then it seems rather pointless to even keep them. So the solution of truncating the string at that decimal point is probably for the best.
Exactly I mean to say. Fractions are always zero. Thats why I want to display my txt box time in this format 00:00 only. May be because the data is linked to server or may be I did not select proper datatype field at server table..dont know. But I need the appearance of time in OTfrm and OTTill field to appear as 00:00 or 00.00 whichever easy.

Any other idea?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:59
Joined
Feb 19, 2013
Messages
16,553
assuming 15:20:00.0000000 underlying value is numeric, try

cTime(left("" & myvalue, instrrev("" & myvalue,".")-1))
 

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
894
Thanks CJLondon,

The data type in SQL Server is time(7).
If there is any other data type I need to select then please advise. It is not too late. I can delete table and create new one as it is initial stage of creating tbls at server.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 19, 2002
Messages
42,970
The SQL Server data type should be DateTime

If you use any other date data type, Access, if it can see it at all, will render it as text and will not work the way you need it to.

The default driver is SQL Server and it is OLD. I think it supports SQL Server data types up to ~ SQL Server 2005. Newer drivers MAY support newer data types but you would have to get the driver distributed to ALL your users to make use of the new data type.

To find the difference between two dates, use the DateDiff() function. Or look at my useful date functions database to see how to work with multiple parts at once.
 

Attachments

  • UsefulDateFunctions20201124b.zip
    300.2 KB · Views: 473

Users who are viewing this thread

Top Bottom