using VBA to convert number into time

hi all. i'm just begining to learn VBA, therefore i've got this question regarding Access and the use of VBA to automate conversions of numbers into time. my test db contains a column that is in a number format but stands for a 24-hr time value (i.e. 233537 means 23:35:37 or 9 means 9AM but without the 09:00:00). however, i need to make this number into the 24 hr format but to MS Access, this number is simply a number and can't be formatted into a time value (esp. without a delimtter). i got a data dump in a form of a text file and imported it into Access (it contains 1.5 million recs). i don't know how to write an SQL query to do this, so i want o automate the process so that in future i can simply run the VBA process after i've imported the data dump into Access. if anyone can help me or direct me to some resources, that would allow me to convert these numbers into a time format that takes into account numbers that are like 233543 and 9 . i thank you in advance for any help and wish you good things. thanks. :wave:
RG

I guess U have to load all the data on to the form and U have to modify the data with one query event and then update in the table,....

so this: 233543 would be 23:35:43 or this: 181227 would be 18:12:27
but this 9 would be 9AM and this 11 wold be 11AM???
if its 1 or 2 digits.. will it always be AM? If I read it right.. if its 9 or 10 you want it to be 09:00:00 or 10:00:00 correct?

you could do somthing like this:
and just use it in an update query....

can the number be 5 digits? 4 digits?

VB Code:

Public Function CTIME(nTime As String) As String
    If Len(nTime) = 2 Then
        CTIME = nTime & ":00:00"
    ElseIf Len(nTime) = 1 Then
        CTIME = "0" & nTime & ":00:00"
    ElseIf Len(nTime) = 6 Then
        CTIME = Left(nTime, 2) & ":" & Mid(nTime, 3, 2) & ":" & Right(nTime, 2)
    End If
End Function

Hi [A51g]Static, firstly thanks heaps for your info and i'm sorry for taking so long to reply had some very ill relative and was away. in reply to your question dated 10-03-2005, 07:28 AM, yes (i.e. if its 9 and not 090000 then it means 9AM). I've got a quick question, how to it put the code you've so kindly written into Access? thanks a lot and i will check back soon. thank you again.
RG