By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

validating Dates and Times

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/a
"Steve" <st****************@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...
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(varDate, 3, 2)), _
CInt(Left(varDate, 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
Nov 12 '05 #3

P: n/a
st****************@hotmail.com (Steve) wrote in
news:ed**************************@posting.google.c om:
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

The dateserial and timeserial functions are one way.

My_date = dateserial(right(text1,4),mid(text1,3,2),left(text 1,2))+
timeserial(left(text2,2),right(text2,2),0)
Bob Q
Nov 12 '05 #4

P: n/a
st****************@hotmail.com (Steve) wrote in message news:<ed**************************@posting.google. 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


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().
Nov 12 '05 #5

P: n/a
TC
st****************@hotmail.com (Steve) wrote in message news:<ed**************************@posting.google. com>...

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


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
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.