Reformat A Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My database looks like this:

Message MessageTime Identifier MessageType
BOX00AIQ0D17 92931 9595959 Receive
BOX00A3S0AU8 92931 4637727 Send
BOX00A460WM1 92931 4637726 Receive
BOX00A460WM1 92931 4637726 Send
BOX00AM50WM3 92931 4637726 Receive
BOX00A0S0N93 92931 4637725 Send

I need to reformat the MessageTime Field to hh:mm:ss.

Can this be done with a query ?

Thank you in advance.
 
Carl

We're not there. What does your "92931" value in the MessageTime field
represent? Total seconds? Seconds since midnight this morning? Is it a
date/time, a time only or a number of days/hours/minutes/seconds/hundredths?

More info, please...

Jeff Boyce
Microsoft Office/Access MVP
 
92931 is 9:29:31 AM. Does that clarify ?

Jeff Boyce said:
Carl

We're not there. What does your "92931" value in the MessageTime field
represent? Total seconds? Seconds since midnight this morning? Is it a
date/time, a time only or a number of days/hours/minutes/seconds/hundredths?

More info, please...

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Possibly, is 92931 your message time in the example records? Do you know
what that represents? Seconds since midnight? or is that supposed to be
9:29:31

If seconds since midnight
TimeValue(DateAdd("s",[MessageTime],0))

TimeValue(DateAdd("s",92931,0)) Returns 1:48:51 AM

Or if it is supposed to 9:29:31 then
TimeValue(Format([MessageTime],"@@:@@:@@"))
and if all you need is to see it displayed with the colons, then drop the
TimeValue from the expression
 
I assume that 92931 means 09:29:31. Yes, you can do it in a query. How,
depends on

-Is it a numeric field or a text field?

-Do you just want to display it that way, or do you want to convert it
into a time value to be stored in an Access date/time field?

The general idea is to use the string manipulation functions such as
Left(), Len(), Mid() and Right() to parse the 92931 into separate hour,
minute and second values. Then either concatenate them with ":" between,
or (to create a time value) pass them to the TimeSerial function.
 
Carl

So what would "731" represent? Or would there be "placeholders" for all
two-digit values (?except for hours?) -- 70301?

Will you be using 24 hour notation? If so, then some MessageTime values
have 5 digits, and others have six?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top