validate text box value is in yyyymmdd format

I have an access text box field that will contain a date field. I need to ensure it is entered in yyyymmdd format

not sure of the  best way to do this
johnnyg123Asked:
Who is Participating?
 
silemoneConnect With a Mentor Commented:
let me know if that works...it should...but

(19|20)[0-9][0-9](0[1-9]|1[12])(0[1-9] |1[0-9]|2[0-9]|3[01])

1900 - 2099 year pattern

01-09   11-12  month pattern

01-09 | 20-29 | 30 31  Day pattern

19000101  should work up to...

20991231
0
 
dbase118Commented:
If the text box is feeding a date/time data type field, you should be able to right click the control and use the format property to define exactly which format you want for the control.
0
 
silemoneCommented:
probably best to do with a regular expression


(19|20)[0-9]{2}[A-Za-z]{2}(0[1-9] |1[0-9]|2[0-9]|3[01])
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dbase118Commented:
Not sure if that format is one of the choices. If not, you can enter it manual in the format field like
yyyy-mm-dd (if you want dashes between month, day, and year
0
 
silemoneCommented:
or you could use a try/catch block

try

dim dt as DateTime = string

catch ex as Exception

MessageBox("Date invalid")

end try
0
 
silemoneCommented:
or you could use stringFormat
0
 
silemoneCommented:
but i think the regular expression way is best...see if string matches pattern, if not, MsgBox("Date Invalid Format")
0
 
johnnyg123Author Commented:
so I would put a check in the BeforeUpdate event
0
 
silemoneCommented:
that event should work...usually it in the event of the button click...but i don't  know how your form is set up...
0
 
johnnyg123Author Commented:
I have the following code

Private Sub invoice_update_date_BeforeUpdate(Cancel As Integer)
   
    Dim re
   
    Set re = New RegExp
   
    re.Pattern = "(19|20)[0-9]{2}[A-Za-z]{2}(0[1-9] |1[0-9]|2[0-9]|3[01])"
   
    re.IgnoreCase = True
   
    If re.Test(Me.ActiveControl.Text) <> True Then
        MsgBox Me.ActiveControl.Text & " is not a valid date" & vbNewLine & "(date must be in yyyymmdd format)"
        Cancel = True
    End If

End Sub

Even if I enter a valid date of 20070606, I get the error message

Any ideas what I might be doing wrong?

I did add reference to microsoft vbscript regular expressions 5.5
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Private Sub YourTextBoxName_AfterUpdate()

Me.YourTextBoxName= Format(Me.YourTextBoxName, "yyyy-mm-dd" )

End Sub

You can also use an Input Mask, but I frown on Input masks for dates - kind of annoying ...

This is the mask:

0000-00-00;;_

mx
0
 
silemoneCommented:
oop...i'm sorry...
i thought mm meant for two letters...


(19|20)[0-9]{2} (0[1-9] | 1[12])(0[1-9] |1[0-9]|2[0-9]|3[01])

try it again...
0
 
johnnyg123Author Commented:
Thanks for all your help silemone....I will give you the points

For any one that might me following this thread

I found an expression that takes in to account leap year  (starting in year 2000 which is fine for me because my app only uses dates from 2005)


"^([2-9]\d{3}((0[1-9]|1[012])(0[1-9]|1\d|2[0-8])|(0[13456789]|1[012])(29|30)|(0[13578]|1[02])31)|(([2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00))0229)$"
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
?  Input mask = no code required.

mx
0
 
silemoneCommented:
hey i could have written that...maybe... :0)...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
??
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Guys ... it's not about the points, but really ... that seems like an extreme solution to force a certain date format in a text box - when a simple input mask on the property sheet of the text box will work.

Am I missing something in the question ?

mx
0
 
silemoneCommented:
Database, reg expression have been around so long because

1) they don't change...won't become deprecated, so once you did it, it's done...
2) they are extremely efficiently...the long line he wrote could have taken way more lines of code to a)validate date and b)make sure leap year was taken into account
3) Quicker because he doesn't need a try/catch block etc...he knows what is entered will be a VALID date
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm sorry ... but the solution is overkill ... and will be misleading to someone else looking at this question. There is no need for all of that when a simple input mask on a property sheet will do exactly the same thing - and leap years are not an issue.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.