[williscm]

I have a substantial program in Excel and went to integrate a data verification in two textboxes and for some reason, the reports were still coming back as data input errors. I have reconstructed the issue in a simple spreadsheet for posting here:

I have one textbox (Textbox1). When focus is lost, a data verification for a date (European (dd/mm/yy)) takes place:

Private Sub TextBox1_LostFocus()
If Not IsDate(TextBox1.Text) Then MsgBox "Not a valid date" Else MsgBox "Valid date"
End Sub

Examples:
Entering 12/12/09 - Valid Date (ie, 12 December 2009)
Entering abc - Not a valid date
Entering 01/08/09 - Valid Date (ie, 01 Aguust 2009)
Entering 14/13/09 - Not a valid date (ie, 14th day of the 13th month 2009)

Unfortunately, when I enter 01/13/09 (ie, the first day of the thirteenth month 2009), it said Valid date. Another example includes a data entry 09/22/09 which should have been entered 22/09/09 so when I run a report for September, it does not pick up 22 as a month and the data is lost.

The Isdate function does not appear to consider the regional setting of the computer (mine is set to Brisbane Australia using dd/mm/yy - no issues there).

I'm a bit lost as to how to fix it. Any ideas?

[Advertisements]

Who wrote the program?

[diabillic]

Who wrote the program?

[williscm]

I wrote the program and recently updated it to validate dates. The sample is simplified but cuts to the problem I wish to solve.

[Jonesey]

You'll need a function to first of all isolate each part of the date and then recombine them.

For this, I've used the datepart VBE function with some success.


Another thing to try is to use the VBE control called DTPicker, which allows the user to select a date from a built in calendar. You might have to install the control into your library.

I found the control installed in C:\WINNT\system32\mscomct2.ocx

Yeah - we're still running Win2K here!!!

Edited by Jonesey, 22 September 2009 - 10:21 AM.

[williscm]

Thanks for the tip.

date part won't work - same issue as before, ie:

Private Sub TextBox1_LostFocus()

Dim Mydate As Date
mymonth = DatePart("m", TextBox1.Text)
If mymonth >= 13 Then MsgBox "Not a valid month" Else MsgBox "Valid month"

End Sub

entering 13/03/09 (13th day of the 3rd month 2009) comes up "Valid month"
entering 03/13/09 (3rd day of the thirteenth month 2009) also comes up "Valid month"

I used the following to pick out the month as an integer from the textbox1. Seems like the only way to do it. I could write a check for each part of a string: one for day, one for month, one for year, to see if it matches a criteria, ie:

Private Sub TextBox1_LostFocus()

'This verifies whether the mid string is a valid month
'by converting the mid string to an integer and checking
'if it is greater or equal to 13
If (Int(Mid(TextBox1.Text, 4, 2))) >= 13 Then MsgBox "Not a valid month" Else MsgBox "Valid month"

End Sub

Unfortunately, I can't install any OCX's where I work :/ Will work on the above.

thanks for the help, all.

[Jonesey]

If you're looking for a way in which valid dates can be entered from a form, another way is to use drop-down, or combo boxes.

Pre-populate three of them with months, days & years and let the user select form the lists.

You could also get a bit clever with it and not populate the "days" box until the month has been selected - then you can set the days box to 31, 30 or 28 (29 on a leap year) depending no which month is selected.

This way you'll NEVER get an invalid date.

Once you've exited the boxes, you can join up the bits using the date function.

How do you know if it's a leap year? If it divides by 4 exactly with no remainder, you have a leap year.(But NOT if it divides exactly by 100!!!)

Edited by Jonesey, 23 September 2009 - 03:56 AM.