Convert time to date?

[Leslie Isaacs]

Hello All

I am using A97.

I have a lot of data where a date field (for date of birth) has been entered
as a time:
i.e. 23 December 2001 has been entered as 23:12:01

My mistake - should have imposed an input mask!

First, is there an expression I can use in an update query to change
23:12:01 to 23/12/01?
Is there any reason I shouldn't use the following:

CDate(FindAndReplace([dob],":","/"))

.... where the FindAndReplace function is...

Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & left(strInString, intPtr -
1) & _
strReplaceString
strInString = Mid(strInString, intPtr +
Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function
(from http://www.mvps.org/access/strings/str0004.htm - thanks to Alden
Streeter)

Second, what Input Mask should I have to ensure that this doesn't happen
again - and should this be set in the table design or in the corresponding
form field?

Hope someone can help.

Many thanks
Les

 

[Jeff Boyce]

Leslie

If the data has been entered into a Date/Time field, what you see displayed
and what is actually stored is probably two different things. Check Format
and data type...

Regards

Jeff Boyce
<Access MVP>

 

[John Spencer (MVP)]

I've been puzzling over this for a few days. If you have already solved it,
then ignore this possible solution.

UPDATE YourTable
SET [Dob] = DateSerial(Second([DOB]) +
IIF(Second([DOB])<6,2000,1900),Minute([DOB]), Hour([Dob]))
WHERE [DOB] is not Null

I adjust the century based on a cutoff of 6. So if the year is before 6 I
assume the year is 2000 to 2005, otherwise the year is assumed to be 1906 to
1999. You can adjust that if you need to.