Run-time error 5 - Invalid procedure call or argument (2 Viewers)

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
852
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?
Code:
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
 

Attachments

  • Pic.jpg
    Pic.jpg
    18.4 KB · Views: 285

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:59
Joined
May 7, 2009
Messages
16,778
i think both X, Y returned as String.
you then convert it to Real Date/Time (CDate() function).
 

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
852
Thanks Arnel,

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

Produce type mismatch....
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:59
Joined
May 7, 2009
Messages
16,778
see your other thread:
 

Ashfaque

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
852
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:59
Joined
May 7, 2009
Messages
16,778
you can use Query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 28, 2001
Messages
23,117
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

Lifelong Learner
Local time
Today, 05:59
Joined
Mar 14, 2017
Messages
6,678
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

Student
Local time
Today, 18:29
Joined
Sep 6, 2004
Messages
852
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.
 

Users who are viewing this thread

Top Bottom