Validating data using a simple query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

My access database has text fields containing date information that
should be in the form DDMMYYYY, and text fields containing time
information that should be in the form HH:MM 24.
ie dates are stored in the form 12101976 and times in the form 20:43

What would be the simpliest query that I could run to validate the
data and indicate which records are not in the correct format?

All ideas welcomed. Thanks in advance
 
Generally, we use the BeforeUpdate Event of the Control on
thr Form to validate the data entry. Assuming that the
short date format is "dd/mm/yyyy" for your regional
settings , you can use an Event like (*untested*):

Private Sub DateControl_BeforeUpdate(Cancel As Integer)

Dim strDate As String

If Len(Me.DateControl) = 8 Then
strDate = Left(Me.DateControl, 2) & "/" & _
Mid(Me.DateControl, 3, 2) & "/"
Right(Me.DateControl, 4)
If IsDate(strDate) = False Then
MsgBox "Something wrong."
Cancel = True
End If
Else
MsgBox "Entry not exactly 8 characters."
Cancel = True
End If
End Sub

You can do similarly for the TimeControl.

Plse note that the above Sub does NOT trap all data entry
errors, for example, "10202003" will probably gets through
even though it is wrong according to your reginal
settings. There are muchmore complex code to test whether
31 is valid for the given month entry, 29 of Feb, etc ...

HTH
Van T. Dinh
MVP (Access)
 
Steve

"Validation" means different things to different folks. I, for example,
might suggest that the string "99999999" could match your "DDMMYYYY"
pattern, but still not be a "valid" date.

Is there a reason why you aren't using an Access Date/Time data type field?
Or maybe converting what you have into a date with the CDate() function?

Format and content are two different subjects in Access.

More info, please...

Jeff Boyce
<Access MVP>
 
Hi jeff,

I need to validate the data to so that they are in the form ddmmyyyy
and the dates are real.
The data was given to me in this form(all in text fields), could I use
the cdate() in a simple select statement

select cdate(column_name) from table_name

or is there a better method.

steve
 
I am not sure of your use of the term "validate" since you already asserted
that "the dates are real". If the dates are real, there is no need to
validate them.

You cannot CDate() on your Text Field. It is NOT a recognisable date format
in Access / VB. Use something similar to my posted code to reconstruct your
date string to a recognisable date-format String.
 
Thanks for catching that, Van. My brain and my "mouth" (fingers) must not
have been connected.

Jeff Boyce
<Access MVP>
 
Back
Top