"Steve" <st************ ****@hotmail.co m> wrote in message
news:ed******** *************** ***@posting.goo gle.com...
I am currently trying to validate data in an access database. I need
to verify that columns containing date information are in the format
ddmmyyyy and columns containg time information are in the format HH:MM
24. The dates and times are stored in text fields. ie date 12121998,
time 20:34
Could anyone suggest a method for validating the fields. What is the
syntax for converting text fields into dates and times?
Any suggestions welcomed. Thanks in advance
You will really have to write your own function for this. You need to
consider whether the length would have to be exactly 8 characters, or
whether 010103 (only 6 characters) is valid and means January 1, 2003. You
should also consider whether there are any dates which cannot be valid - for
example should an order date of 01011492 be viewed with suspicion?
For example, the function below not only checks if the date is valid (must
be exactly 8 characters) but also that it occurs between 1980 and 2003 if it
does, a date is returned, otherwise the function returns null.
HTH
Fletcher
Public Function GetDate(varDate ) As Variant
On Error Resume Next
Dim dte As Date
Dim var As Variant
var = Null
If Len(varDate) = 8 Then
dte = DateSerial(CInt (Right(varDate, 4)), _
CInt(Mid(varDat e, 3, 2)), _
CInt(Left(varDa te, 2)))
End If
If Err.Number = 0 Then
If dte > DateSerial(1980 , 1, 1) And _
dte < DateSerial(2004 , 1, 1) Then _
var = dte
End If
GetDate = var
End Function
