Converting AccPac Time to VB time formate using VB/VBA

[Naureen1995]

I am trying to convert the Time field of AccPac 5.0A running on Pervasive 2000I using VB. Is there any simple way?
Thanks

 

[zemp]

Convert it to what? Is it the same convertion for every field? Do you want to reassing the converted values back to the fields?

From VB it would be easiest to use the xAPI or COMAPI supplied by ACCPAC. It uses the ACCPAC views which hold the ACCPAC business logic and business rules. This will ensure data integrity.

You can search this forum for 'xAPI' and/or 'COMAPI' to get some more information on these objects.

zemp

 

[Naureen1995]

Thanks Zemp

I do not want to save the data back to AccPac. I only need to know when a user made the specific change/transaction. I just want to convert the AccPac time into a clock time like 12.30Am.

 

[zemp]

Naureen1995,

The time, in the 'AUDTTIME' fields for example, is stored in digit pairs as hours, minutes, seconds and hundreths of a second.

For example a time field may have a value of '13342267'.

This is read as

13 hours
34 minutes
22 Seconds
67 100 ths of a second (67/100)

You will notice that it is also stored with 24 hours, so you will need to do some math to get the AM ot PM.

Also the time is GMT (Greenwich Mean Time). This means that you will have to adjust the time for your specific time zone. So for the Eastern Standard Time (GMT -5.00 hours) you would have to subtract the 5 hour difference.

Thus the time would be 8:34.22 AM.

You can try using a function similar to the one below to return the time correctly. Don't forget to make the correct adjustment for your time zone.

Private Function Format_Time(Value As Variant) As String
   Dim Hours As Integer
   Dim Minutes As Integer
   Dim Seconds As Integer
   
   Hours = Left(Value, 2) - 5 '5 hour difference for EST.
   Minutes = Mid(Value, 3, 2)
   Seconds = Mid(Value, 5, 2)
   If Hours < 12 Then
      Format_Time = Hours & ":" & Format(Minutes, "00") & "." & Format(Seconds, "00") & " AM"
   Else
      If Hours > 12 Then
         Format_Time = Hours - 12 & ":" & Format(Minutes, "00") & "." & Format(Seconds, "00") & " PM"
      Else
         Format_Time = Hours & ":" & Format(Minutes, "00") & "." & Format(Seconds, "00") & " PM"
      End If
   End If
End Function

zemp

 

[Naureen1995]

Thank you very much Zemp. It works.

 

[Naureen1995]

Zemp the code given by you works well on a 8 digits time. But, in my accpac data I have Audttime field ranging from 5 - 8 digits. For example one of the audttime in my accpac data is 24384. I suppose if the time is 5 digits then the transanction is done just after midnight (24 hours). I correct the time according to my time zone.
Thanks

 

[zemp]

Good point, seems that the time can be placed into the database as '00024384' and the zeros are truncated. After all 00123 = 123, from a numerical standpoint.

zemp