Unanswered: Access 2000: Converting a 12 digit string into date/time data type
I was wondering if anyone could give me alittle help on a problem I am having with Microsoft Access 2000.
I have a column in a table populated with 12 digit strings.
(For example: 040705151832)
These number represent both a concatenated date and time.
I would like to set this string into a 'General Date' to look something like this:
Now, the problem is that every time I go into Design view and set the Data type to Date/Time to format 'General Date', Access says the records were deleted for that column. Does anyone have any ideas on how I could accomplish this conversion from string to date/time?
ps. I am a real notice on Microsoft Access, so any help would be greatly appreciated.
with a small amount of coding, you could write a function that performs the conversion for you. Is this a one time conversion, or something you'll need to do over and over?
try this: create a new form and place this in the code for the click event of a button:
Dim strS() As String
Dim iC As Integer
Dim dtVal As Date
Dim rsDat As DAO.Recordset
Set rsDat = CurrentDb.OpenRecordset("Select [StringDateField], [NewDateField] From YourTable")
While Not rsDat.EOF
' Assume the string is in the format: YYMMDDHHmmss
' Pick apart the string into seperate pieces
For iC = 1 To 6
strS(iC) = Mid$(rsDat(0) & " ", (iC * 2 - 1), 2)
' Put it back together in a recognizable date format3
strS(7) = strS(2) & "/" & strS(3) & "/" & strS(1) & " " & strS(4) & ":" & strS(5) & ":" & strS(6)
' Write is back to the table in the new field
rsDat(1) = dateValue(strS(7))
Set rsDat = Nothing
Greetings tcace, thanks for the help thus far. In reply to our question I intend import the data from a text file, which has this undesirable string format. I would like to be able to import all that data so that it is in a general date/time format. I need to repeat this for the other imports I do. Will this function accomplish this? Could you give more detailed instructions on setting up this function, I have never set a function before. Thank you for your help and I appreciate all the help you have given me so far.