Run-time error 5 - Invalid procedure call or argument

[Ashfaque]

Hi,
When I enter the time in any of the field it produces above error but after few seconds it calculates correctly with no error.

I dont have any idea of Server linked tbls data. I have recently moved all Access BE tbls to SQL Server and linked in Access FE and I am trying to make the db full worked..

Also I dont want to display such kind of lengthy format of time in my Access form.

Is there any solutions for both?

Private Sub OTTill_AfterUpdate()

If IsNull(OTFrm) Then
OTFrm = Nz(OTFrm, 0)
Else
Dim X, Y
X = Left(OTFrm, InStr(OTFrm, ".") - 1)
Y = Left(OTTill, InStr(OTTill, ".") - 1)
OTTotHrs = (DateDiff("n", X, Y)) / 60
MsgBox ("Total Hrs : ") & OTTotHrs
End If
End Sub

Please advise

 

[arnelgp]

i think both X, Y returned as String.
you then convert it to Real Date/Time (CDate() function).

 

[Ashfaque]

Thanks Arnel,

When I tried :
OTTill = CDate(OTTill)
OTFrm = CDate(OTFrm)

Produce type mismatch....

 

[arnelgp]

see your other thread:

Time Calculation - Run-time error 13 - Type mismatch

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 =...

www.access-programmers.co.uk

 

[Ashfaque]

Yes I know. But now my major concern is know how can these time fields of server table can be displayed on Access sub form in the format we need i.e. 00:00 instead of a lengthy one with I attached in the pic. Secondly, I need to calculate hrs and minutes based on these 2 field and take into the rird.

 

[arnelgp]

you can use Query.

 

[The_Doc_Man]

It is absolutely impossible using ordinary Access date/time routines to manage what appears to be a time in some variant of UTC formatting. You need an external library or a user-defined function to manage ANY times that show time to ANY fraction of a second. ArnelGP thinks your times are coming back as strings. I KNOW that they must come back as strings because if they WERE coming back as date/time, Access would neither show the decimal point nor the fractions of a second, even if they WERE all zero. What you show in the attachment in post #1 of this thread is NOT an Access format. The only way to make this work is to remove the decimal point and everything to the right of it, after which a CDate() function should finish the conversion. Then the Access formatting routines can handle it AND you can start doing computations as you were hoping to do.

You can try to use InStr(,,) to find the decimal point (and there should only be one per string), then take a Left() of everything to the left of that position. I leave it to you to look up those functions.

 

[Isaac]

I dont have any idea of Server linked tbls data. I have recently moved all Access BE tbls to SQL Server and linked in Access FE

How can both of those statements be true? You moved all your data to sql server, but you have no idea what data might be in the tables?

 

[Ashfaque]

How can both of those statements be true? You moved all your data to sql server, but you have no idea what data might be in the tables?

It is because the data fields are different than what till date we used in MS ACCESS. Server field behavior looks different than Access. No idea why it saves in different format. This what I mean.