Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Turning 0800 text to 08:00:00 AM in access 4
- Thread starter B555
- Start date
-
2
- #3
-
1
- #4
I would create a module and put the following function in it
Public Function ConvertMilTime(strec As String) As Date
strec = Left(strec, 2) & ":" & Right(strec, 2)
ConvertMilTime = Format(strec, "Long Time")
End Function
I added the step of converting the received time to a string with the ":" simply for your understanding of how it would work
Then you query would look like this.
SELECT Field1, Field2, ConvertMilTime(Field3) AS MyTime
FROM Table1
Replace as follows
Field 1 and 2 are simply other fields you want in your query.
Field 3 is the name fo the field you want to convert to normal time.
Table 1 is the table to pull the data from.
The only thing I would add to the function is a check at the beginning to account for
1. Nulls - Assume 0001 for one minute past midnight
2. 0000 - which is not a valid time. Maybe add a one to the end to make it also one minute past midnight
Andy
Andy Baldwin
"Testing is the most overlooked programming language on the books!
Public Function ConvertMilTime(strec As String) As Date
strec = Left(strec, 2) & ":" & Right(strec, 2)
ConvertMilTime = Format(strec, "Long Time")
End Function
I added the step of converting the received time to a string with the ":" simply for your understanding of how it would work
Then you query would look like this.
SELECT Field1, Field2, ConvertMilTime(Field3) AS MyTime
FROM Table1
Replace as follows
Field 1 and 2 are simply other fields you want in your query.
Field 3 is the name fo the field you want to convert to normal time.
Table 1 is the table to pull the data from.
The only thing I would add to the function is a check at the beginning to account for
1. Nulls - Assume 0001 for one minute past midnight
2. 0000 - which is not a valid time. Maybe add a one to the end to make it also one minute past midnight
Andy
Andy Baldwin
"Testing is the most overlooked programming language on the books!
-
1
- Thread starter
- #7
Well if he is in France then he should consider that. For the rest of us, 00:00:01 or 00:00:00:01 (one second past midnight) would be the starting point of a 24 hour day. No military in the world that I have worked with represents any time of 00:00:00. France may be different since I have never had the opprotunity to work with the French military.
The only reason it works in VBA is due to lazy programming on the part of MS (imagine that) in developing their functions.
Andy.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
The only reason it works in VBA is due to lazy programming on the part of MS (imagine that) in developing their functions.
Andy.
Andy Baldwin
"Testing is the most overlooked programming language on the books!
There must be a lot lazy people ...
The ANSI standard for DateTime columns:
DATE values are represented according to the Gregorian calendar and TIME
values are represented according to the 24 hour clock. The inclusive value
limits for the DATETIME fields are as follows:[tt]
YEAR 0001 to 9999
MONTH 01 to 12
DAY 01 to 31 (upper limit further constrained by MONTH and YEAR)
HOUR 00 to 23
MINUTE 00 to 59
SECOND 00 to 59.999999999[/tt]
The ANSI standard for DateTime columns:
DATE values are represented according to the Gregorian calendar and TIME
values are represented according to the 24 hour clock. The inclusive value
limits for the DATETIME fields are as follows:[tt]
YEAR 0001 to 9999
MONTH 01 to 12
DAY 01 to 31 (upper limit further constrained by MONTH and YEAR)
HOUR 00 to 23
MINUTE 00 to 59
SECOND 00 to 59.999999999[/tt]
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question