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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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 Architect / Systems AnalystCommented:
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
silemoneCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
?  Input mask = no code required.

mx
0
silemoneCommented:
hey i could have written that...maybe... :0)...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
??
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.