Convert a number to a date time via unbound feilds

[GLT]

Hi,

I have two unbound fields, called runDate and runTime on my form.

I extract the run date and run time from the RecID field (which is the
primary key). This primary key is created by a script that outside of my DB
(i.e. I import the data).

I can extract the date and time which look like this:

24012010 = 24/01/2010
235336 = 23:53:36

Now I would like to convert these numbers to their respective formats (i.e.
24/01/2010 and 23:53:36) and would like these fields to update whenever the
record changes...

I have used the following code at the DataChange event of the form, however
the form does not update, and the msgbox's produce an overflow error. Can
anyone advise how to fix this?

Cheers,
GLT.

Private Sub Form_DataChange(ByVal Reason As Long)

Dim rTime, rDate As String

rDate = Format(Left((Right([RecID], 14)), 8), "dd/mm/yyyy")
rTime = Format(Right((Right([RecID], 14)), 6), "hh:mm AMPM")

MsgBox rTime
MsgBox rDate
End Sub

 

[John W. Vinson]

I have two unbound fields, called runDate and runTime on my form.

I extract the run date and run time from the RecID field (which is the
primary key). This primary key is created by a script that outside of my DB
(i.e. I import the data).

I can extract the date and time which look like this:

24012010 = 24/01/2010
235336 = 23:53:36

Now I would like to convert these numbers to their respective formats (i.e.
24/01/2010 and 23:53:36) and would like these fields to update whenever the
record changes...

I have used the following code at the DataChange event of the form, however
the form does not update, and the msgbox's produce an overflow error. Can
anyone advise how to fix this?

Cheers,
GLT.

Private Sub Form_DataChange(ByVal Reason As Long)

Dim rTime, rDate As String

rDate = Format(Left((Right([RecID], 14)), 8), "dd/mm/yyyy")
rTime = Format(Right((Right([RecID], 14)), 6), "hh:mm AMPM")

It seems that the date and time are in the rightmost 14 bytes of this RecID
field. Is RecID a Text field (I assume it must be, that's a lot of digits
already?

You should be able to set the control source of a (single) textbox on your
form to

=DateSerial(Mid(Right([RecID], 14), 5, 4), Mid(Right([RecID], 14), 3, 2),
Left(Right([RecID], 14), 2)) + CDate(Format(Right([RecID], 4), "@@@"))

Untested code - you may want to try it out in the Immediate window.

 

[GLT]

Hi John,

Thanks for you rreply, it worked perfectly except I had to add an extra @
as follows:

=DateSerial(Mid(Right([RecID], 14), 5, 4), Mid(Right([RecID], 14), 3, 2),
Left(Right([RecID], 14), 2)) + CDate(Format(Right([RecID], 4), "@@@@"))

I have two unbound fields, called runDate and runTime on my form.

I extract the run date and run time from the RecID field (which is the
primary key). This primary key is created by a script that outside of my DB
(i.e. I import the data).

I can extract the date and time which look like this:

24012010 = 24/01/2010
235336 = 23:53:36

Now I would like to convert these numbers to their respective formats (i.e.
24/01/2010 and 23:53:36) and would like these fields to update whenever the
record changes...

I have used the following code at the DataChange event of the form, however
the form does not update, and the msgbox's produce an overflow error. Can
anyone advise how to fix this?

Cheers,
GLT.

Private Sub Form_DataChange(ByVal Reason As Long)

Dim rTime, rDate As String

rDate = Format(Left((Right([RecID], 14)), 8), "dd/mm/yyyy")
rTime = Format(Right((Right([RecID], 14)), 6), "hh:mm AMPM")

It seems that the date and time are in the rightmost 14 bytes of this RecID
field. Is RecID a Text field (I assume it must be, that's a lot of digits
already?

You should be able to set the control source of a (single) textbox on your
form to

=DateSerial(Mid(Right([RecID], 14), 5, 4), Mid(Right([RecID], 14), 3, 2),
Left(Right([RecID], 14), 2)) + CDate(Format(Right([RecID], 4), "@@@"))

Untested code - you may want to try it out in the Immediate window.