Validating month and day in a date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a procedure for a table field where data is input via a text transfer and the field is text but will be the value of a date in yymmdd format. I want to validate the separate portions of month and day to be able to report if the date is valid or not. (It will also include checking if the field is numeric or is null to further identify the data as valid.)

Any help is welcome. Thanks!
 
Mike said:
I need a procedure for a table field where data is input via a text transfer and the field is text but will be the value of a date in yymmdd format. I want to validate the separate portions of month and day to be able to report if the date is valid or not. (It will also include checking if the field is numeric or is null to further identify the data as valid.)
Click to expand...


You can use logic along these lines to do some of that:

If Nz(Me!thefield, "") = "" Then
' the field is either Null or a zero length string
ElseIf Me!thefield Like "*[!0-9]*" Then
' thefield is not numeric
Else
intYear = CInt(Left(Me!thefield, 2))
intMonth = CInt(Mid(Me!thefield, 3, 2))
intDay = Cint(Right(Me!thefield, 2))
' Check if a valid date
If IsDate(intmonth & "/" & intDay & "/" & intYear)
Then
dtDate = DateSerial(intYear, intMonth, intDay)
Else
'Not a valid date
End If
End If
 
Use the Mid function to break the value into its component parts:

Function ValidDate(StringDate As String) As Boolean
On Error Resume Next

Dim dtmDate As Date
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer

If Len(StringDate) = 6 Then
If IsNumeric(StringDate) Then
intYear = CInt(Left(StringDate, 2))
intMonth = CInt(Mid(StringDate, 3, 2))
intDay = CInt(Right(StringDate, 2))
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)
End If
End If
End Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Mike Binger said:
I need a procedure for a table field where data is input via a text
Click to expand...
transfer and the field is text but will be the value of a date in yymmdd
format. I want to validate the separate portions of month and day to be
able to report if the date is valid or not. (It will also include checking
if the field is numeric or is null to further identify the data as valid.)
 
Douglas J. Steele wrote:

[]
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)
Click to expand...


Careful Doug. It's kinda difficult to get an error out of
DateSerial:

?DateSerial(99,99,99)
6/7/107
 
Marshall Barton said:
Douglas J. Steele wrote:

[]
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)
Click to expand...


Careful Doug. It's kinda difficult to get an error out of
DateSerial:

?DateSerial(99,99,99)
6/7/107
Click to expand...

Good point, Marshall.

In actual fact, I'd intended to write logic that checked If intMonth > 0 And
intMonth < 13 Then, but I decided it was going to take too long to type in
the code to validate the value for intDay (I couldn't find an
already-written version in the sample database I use while posting)

Guess I shouldn't have bothered responding if I was going to be so lazy.

OTOH, I'm not sure you can rely on IsDate(intmonth & "/" & intDay & "/" &
intYear) either. If intmonth is greater than 12, it'll still pass if intDay
is less than 13. (In other words, it'll try dd/mm/yy if it's not valid as
mm/dd/yy, and say it's good if it's okay in that format)
 
Douglas said:
[]
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)
Click to expand...

"Marshall Barton" wrote
Careful Doug. It's kinda difficult to get an error out of
DateSerial:

?DateSerial(99,99,99)
6/7/107
Click to expand...
Click to expand...
Douglas said:
Good point, Marshall.

In actual fact, I'd intended to write logic that checked If intMonth > 0 And
intMonth < 13 Then, but I decided it was going to take too long to type in
the code to validate the value for intDay (I couldn't find an
already-written version in the sample database I use while posting)

Guess I shouldn't have bothered responding if I was going to be so lazy.

OTOH, I'm not sure you can rely on IsDate(intmonth & "/" & intDay & "/" &
intYear) either. If intmonth is greater than 12, it'll still pass if intDay
is less than 13. (In other words, it'll try dd/mm/yy if it's not valid as
mm/dd/yy, and say it's good if it's okay in that format)
Click to expand...


Another good point, Doug.

I'm too lazy to write the day verification code too ;-)

Scratching head now . . . {light bulb over head}
How about this:

IsDate(Format(intYear, "\2\000") & "-" & intMonth & "-" &
intDay)
 
Marshall Barton said:
Douglas J. Steele wrote:

[]
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)

"Marshall Barton" wrote
Careful Doug. It's kinda difficult to get an error out of
DateSerial:

?DateSerial(99,99,99)
6/7/107
Click to expand...
Click to expand...
Douglas said:
Good point, Marshall.

In actual fact, I'd intended to write logic that checked If intMonth > 0 And
intMonth < 13 Then, but I decided it was going to take too long to type in
the code to validate the value for intDay (I couldn't find an
already-written version in the sample database I use while posting)

Guess I shouldn't have bothered responding if I was going to be so lazy.

OTOH, I'm not sure you can rely on IsDate(intmonth & "/" & intDay & "/" &
intYear) either. If intmonth is greater than 12, it'll still pass if intDay
is less than 13. (In other words, it'll try dd/mm/yy if it's not valid as
mm/dd/yy, and say it's good if it's okay in that format)
Click to expand...


Another good point, Doug.

I'm too lazy to write the day verification code too ;-)

Scratching head now . . . {light bulb over head}
How about this:

IsDate(Format(intYear, "\2\000") & "-" & intMonth & "-" &
intDay)
Click to expand...

That'll probably work. I was going to complain that it wouldn't work for
past dates (such as birthdays) because of forcing the year to 20xx, but
since the intent is validation only, as opposed to actual conversion, it's
probably okay. Of course, it'll wrongly indicate that 29 Feb, 1900 is
valid...

OTOH, the day validation isn't that bad:

Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
If intDay < 1 Or intDay > 31 Then
' Invalid
End If
Case 2
If intYear Mod 4 = 0 Then
If (intYear Mod 400 = 0) Or (intYear Mod 100 <> 0) Then
If intDay < 1 Or intDay > 29 Then
' Invalid
End If
Else
If intDay < 1 Or intDay > 28 Then
' Invalid
End If
End If
Else
If intDay < 1 Or intDay > 28 Then
' Invalid
End If
End If
Case 4, 6, 9, 11
If intDay < 1 Or intDay > 30 Then
' Invalid
End If
Case Else
' Invalid
End Select

I've put the full logic for leap years there. Since the OP indicated only 2
digit year, it's probably not correct.
 
Douglas said:
[]
dtmDate = DateSerial(intYear, intMonth, intDay)
ValidDate = (Err.Number = 0)


"Marshall Barton" wrote
Careful Doug. It's kinda difficult to get an error out of
DateSerial:

?DateSerial(99,99,99)
6/7/107
Click to expand...
Douglas said:
Good point, Marshall.

In actual fact, I'd intended to write logic that checked If intMonth > 0 And
intMonth < 13 Then, but I decided it was going to take too long to type in
the code to validate the value for intDay (I couldn't find an
already-written version in the sample database I use while posting)

Guess I shouldn't have bothered responding if I was going to be so lazy.

OTOH, I'm not sure you can rely on IsDate(intmonth & "/" & intDay & "/" &
intYear) either. If intmonth is greater than 12, it'll still pass if intDay
is less than 13. (In other words, it'll try dd/mm/yy if it's not valid as
mm/dd/yy, and say it's good if it's okay in that format)
Click to expand...

"Marshall Barton" wrote
Another good point, Doug.

I'm too lazy to write the day verification code too ;-)

Scratching head now . . . {light bulb over head}
How about this:

IsDate(Format(intYear, "\2\000") & "-" & intMonth & "-" &
intDay)
Click to expand...
Click to expand...
Douglas said:
That'll probably work. I was going to complain that it wouldn't work for
past dates (such as birthdays) because of forcing the year to 20xx, but
since the intent is validation only, as opposed to actual conversion, it's
probably okay. Of course, it'll wrongly indicate that 29 Feb, 1900 is
valid...

OTOH, the day validation isn't that bad:

Select Case intMonth
Case 1, 3, 5, 7, 8, 10, 12
If intDay < 1 Or intDay > 31 Then
' Invalid
End If
Case 2
If intYear Mod 4 = 0 Then
If (intYear Mod 400 = 0) Or (intYear Mod 100 <> 0) Then
If intDay < 1 Or intDay > 29 Then
' Invalid
End If
Else
If intDay < 1 Or intDay > 28 Then
' Invalid
End If
End If
Else
If intDay < 1 Or intDay > 28 Then
' Invalid
End If
End If
Case 4, 6, 9, 11
If intDay < 1 Or intDay > 30 Then
' Invalid
End If
Case Else
' Invalid
End Select
Click to expand...


Ok, I give up, you're not as lazy as I am ;-)
 
Aaargh. Why didn't we think of that?

You've obviously been spending too much time dealing with conversions
recently, John, haven't you? <g>

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



John Marshall said:
Why not round trip it?

Convert String to date with DateSerial.
Convert the date back to text.

If the string returned is the same as the initial string, then the date is
valid.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
Click to expand...
 
What every gave you that idea? One slow mainframe database to a 200meg
Access database that fits on a USB drive that fits on my keychain. The fun
of progress.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
Douglas J. Steele said:
Aaargh. Why didn't we think of that?

You've obviously been spending too much time dealing with conversions
recently, John, haven't you? <g>
Click to expand...
 
Actually I stumbled upon this method and have decided to use it. Thanks so much for your help

----- John Marshall, MVP wrote: ----

Why not round trip it

Convert String to date with DateSerial
Convert the date back to text

If the string returned is the same as the initial string, then the date i
valid

John... Visio MV

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.ht
Need VBA examples? http://www.mvps.org/visio/VBA.ht
Common Visio Questions http://www.mvps.org/visio/common_questions.ht
Marshall Barton said:
Careful Doug. It's kinda difficult to get an error out o DateSerial
6/7/10
--
Click to expand...
Mars
MVP [MS Access
Click to expand...
 
Back
Top