validating Dates and Times

[Steve]

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

[David Aston]

Re: validating Dates and Times

I recently experienced a similar problem although I was not able to
find a satisfactory solution. Lets hope that atleast one person has
some ideas.

[Fletcher Arnold]

Re: validating Dates and Times

"Steve" <stevegarringto n101@hotmail.co m> wrote in message
news:ed6c27ab.0 310200907.22e9c b9d@posting.goo gle.com...[color=blue]
> 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[/color]


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

[Bob Quintal]

Re: validating Dates and Times

stevegarrington 101@hotmail.com (Steve) wrote in
news:ed6c27ab.0 310200907.22e9c b9d@posting.goo gle.com:
[color=blue]
> 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[/color]


The dateserial and timeserial functions are one way.

My_date = dateserial(righ t(text1,4),mid( text1,3,2),left (text1,2))+
timeserial(left (text2,2),right (text2,2),0)


Bob Q

[Pieter Linden]

Re: validating Dates and Times

stevegarrington 101@hotmail.com (Steve) wrote in message news:<ed6c27ab. 0310200907.22e9 cb9d@posting.go ogle.com>...[color=blue]
> 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[/color]

I'm almost afraid to ask this, but why not just store them as normal
date/time fields in Access and then use date math to convert them to
some other format by using a query? Seems like you're making work for
yourself by working against the way the database works. Only way of
validating the fields like that is to use a function in the
BeforeInsert event of your form and disallow adding records directly
to the table.

See CDate()and DateSerial().

[TC]

Re: validating Dates and Times

stevegarrington 101@hotmail.com (Steve) wrote in message news:<ed6c27ab. 0310200907.22e9 cb9d@posting.go ogle.com>...

(snip)
[color=blue]
> I need to verify that columns containing date information
> [in a text field] are in the format ddmmyyyy[/color]

Why? Store dates in Date fields: that is what Date fields are for.
When you want to display them, use the Format property to make them
look however you want. That is what the Format property is for.

HTH,
TC