Client has dates in text field - help to compare?

[law32908]

I have this client who's database was sloppily set up. They have asked me to do several queries - based on a text field that has a date in it - called ProcessNum.

Example ProcessNum: 0803011503 Stands for 08/03/01 - time 15:03.

As I said, this is in a text field. I have tried everything in my capabilities to convert this field to date/time - so I can compare dates, but my knowledge of VB is limited. Everything I have tried causes the fields to be deleted.

What can I do???

Thanks so much.
Laura

 

[Darrylles]

Hi Law,

You have said that you tried everything, but you haven't listed your attempts specifically - so I'm guessing your level of knowledge.

Seems like the data is correct for a long date.
Copy the table to a new 'backup' name. (So you've got the original).
Go into the table design and convert the 'text' date field to data type 'date time' and a format of 'long date'.

Should work ok.

Sorry if no help with this, but you'll get a solution here if not.

Regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[law32908]

Thanks, Darrylle. I should have been more clear on what I did!

I tried changing the date format of the table - with all different kinds of formats (sort of a trial and error)

I tried creating a new table with the right format and appending the data to it with a query.

I tried inserting "/" in the correct places - and then converting it.

Basically - I tried most things I could do without getting into any type of code.


I gave your suggestion a try - and it deleted all the data in that field - in all the records.

I feel like I am missing something obvious - but of course - I don't know what!

Laura

 

[BriceR]

This is crude but it works - this is for a date in a text field - ie 40402 (4th april 2002). In your case you will need to use the mid() function for your whole date and the right() function for your time - right(time,4) and use the mid() function to build your time - Time = mid(date,7,2) & ":" & right(date,2). Hope this helps.

On Error GoTo PROC_ERR
Dim varX
Dim varY
Dim varZ
Dim rstTrSet1 As Recordset
Dim rstTrSet2 As Recordset

Set rstTrSet1 = CurrentDb.OpenRecordset("ClientTable", dbOpenDynaset)
Set rstTrSet2 = CurrentDb.OpenRecordset("NewTable", dbOpenDynaset)
rstTrSet1.MoveLast
rstTrSet1.MoveFirst
Do Until rstTrSet1.EOF
If Len([rstTrSet1]![Date]) = 6 Then
varX = Left([rstTrSet1]![Date], 2) & "/"
varX = varX & Mid([rstTrSet1]![Date], 3, 2) & "/"
varX = varX & Right([rstTrSet1]![Date], 2)
Else
varX = Left([rstTrSet1]![Date], 1) & "/"
varX = varX & Mid([rstTrSet1]![Date], 2, 2) & "/"
varX = varX & Right([rstTrSet1]![Date], 2)
End If
varX = Format(varX, "short date&quot
rstTrSet2.AddNew
rstTrSet2!Date = varX
rstTrSet2.Update
rstTrSet1.MoveNext
varX = Null
Loop
rstTrSet1.close
rstTrSet2.close
Set rstTrSet1 = Nothing
Set rstTrSet2 = Nothing
Exit Sub

PROC_ERR:
MsgBox "The following error occured: " & Error$
Resume Next