Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

validate text box value is in yyyymmdd format

Posted on 2008-11-07
19
Medium Priority
?
434 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:johnnyg123
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 15

Expert Comment

by:dbase118
ID: 22905431
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
 
LVL 21

Expert Comment

by:silemone
ID: 22905453
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
 
LVL 15

Expert Comment

by:dbase118
ID: 22905465
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:silemone
ID: 22905481
or you could use a try/catch block

try

dim dt as DateTime = string

catch ex as Exception

MessageBox("Date invalid")

end try
0
 
LVL 21

Expert Comment

by:silemone
ID: 22905494
or you could use stringFormat
0
 
LVL 21

Expert Comment

by:silemone
ID: 22905516
but i think the regular expression way is best...see if string matches pattern, if not, MsgBox("Date Invalid Format")
0
 

Author Comment

by:johnnyg123
ID: 22905744
so I would put a check in the BeforeUpdate event
0
 
LVL 21

Expert Comment

by:silemone
ID: 22905786
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
 

Author Comment

by:johnnyg123
ID: 22906236
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
 
LVL 75
ID: 22906342
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
 
LVL 21

Expert Comment

by:silemone
ID: 22906494
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
 
LVL 21

Accepted Solution

by:
silemone earned 2000 total points
ID: 22906538
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
 

Author Comment

by:johnnyg123
ID: 22906819
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
 
LVL 75
ID: 22907395
?  Input mask = no code required.

mx
0
 
LVL 21

Expert Comment

by:silemone
ID: 22907572
hey i could have written that...maybe... :0)...
0
 
LVL 75
ID: 22908502
??
0
 
LVL 75
ID: 22908584
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
 
LVL 21

Expert Comment

by:silemone
ID: 22908822
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
 
LVL 75
ID: 22908862
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question