Time Calculation - Run-time error 13 - Type mismatch

[Ashfaque]

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]

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]

you can also create a public function to convert the string time to real time:

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:

Dim X
X = DateDiff("n", cnvToAccessTime(OTFrm), cnvToAccessTime(OTTill)) \ 60
OTTotHrs=X

 

[Ashfaque]

As is, this is not time. Remove the decimal value.

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

Excellent....Thanks Ranman256

 

[Ashfaque]

you can also create a public function to convert the string time to real time:

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:

Dim X
X = DateDiff("n", cnvToAccessTime(OTFrm), cnvToAccessTime(OTTill)) \ 60
OTTotHrs=X

Fantastic idea......Thanks Arnel....

 

[The_Doc_Man]

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]

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]

assuming 15:20:00.0000000 underlying value is numeric, try

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

 

[Ashfaque]

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.

 

[arnelgp]

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

?ctime()

 

[Ashfaque]

Error.....Function Or Sub not defined....

 

[Pat Hartman]

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.

 

[CJ_London]

?ctime()

sorry, meant cDate