If Not Like - Checking string for date format

[reyreyreyes]

Hi all

I keep coming back with more confusion -- sorry!

I'm trying to validate a date entry entered in an inputbox (it is entered as a string which I convert to a date later -- that works, but it's just the format checking whilst the date is a string that I have trouble with). Here's what I have, that doesn't work (no error, it just executes every line even when a date is like what I have set). Here's the code:



Private Sub CDateAus_Click()
Dim DateEntry As String
Dim CDateAus As Date
CDateAus = Date
DateEntry = InputBox("Please enter an alternative date in dd/mm/yy format", "Date Entry")

If Not DateEntry Like "[#][#]/[#][#]/[#][#]" Then
If Not DateEntry Like "[#][#]/[#]/[#][#]" Then
If Not DateEntry Like "[#]/[#]/[#][#]" Then
If Not DateEntry Like "[#]/[#]/[#][#]" Then
'If Not DateEntry Like "[#][#]/[#][#]/[#][#]" Or Not DateEntry Like "[#][#]/[#]/[#][#]" Or Not DateEntry Like "[#]/[#]/[#][#]" Or Not DateEntry Like "[#]/[#]/[#]" Then

MsgBox "Date format not accepted. Please enter as dd/mm/yy or dd/mm/yyyy"
Exit Sub
End If
End If
End If
End If



There are multiple 'If' line sas you can see, but I've tried this on just the one if statement at the top, and then entered a date in dd/mm/yy format or dd/mm/yyyy format, and it responds in the same way.

Can anybody help?

Thanks.

[Bob Phillips]

Private Sub CDateAus_Click()
Dim DateEntry As String
Dim CDateAus As Date

CDateAus = Date
DateEntry = InputBox("Please enter an alternative date in dd/mm/yy format", "Date Entry")

If Not DateEntry Like "##/##/##" And Not DateEntry Like "##/##/####" And _
Not DateEntry Like "##/#/##" And Not DateEntry Like "##/#/####" And _
Not DateEntry Like "#/#/##" And Not DateEntry Like "#/##/####" And _
Not DateEntry Like "#/#/##" And Not DateEntry Like "#/#/####" Then

MsgBox "Date format not accepted. Please enter as [d]d/[m]mm/[yy]yy,"
Exit Sub
End If
End Sub

[reyreyreyes]

Thank you for the reply.

Aside from my schoolboy syntax error using square-brackets, how come 'And' is used instead of 'Or'? Won't 'And' look for the entry conforming to both formats?

Either way, I will try the code when I get home.

[Paul_Hossler]

You can try something like this also



Option Explicit

Private Sub CDateAus_Click()
Dim DateEntry As String
Dim CDateAus As Date
CDateAus = Date
DateEntry = InputBox("Please enter an alternative date in dd/mm/yy format", "Date Entry")

If DateEntry Like "[ 1-3][0-9]/[ 1][0-2]/[1-2][0-9]" Then GoTo DateOK
If DateEntry Like "[ 1-3][0-9]/[ 1][0-2]/[1-2][0-9][0-9][0-9]" Then GoTo DateOK

If IsDate(DateEntry) Then GoTo DateOK

MsgBox "Date format not accepted. Please enter as dd/mm/yy or dd/mm/yyyy"

Exit Sub
DateOK:

MsgBox "Date format accepted!!! " & Format(DateEntry, "dd/mm/yyyy")

End Sub

[Bob Phillips]

Thank you for the reply.

Aside from my schoolboy syntax error using square-brackets, how come 'And' is used instead of 'Or'? Won't 'And' look for the entry conforming to both formats?

Either way, I will try the code when I get home.

I think your idea behind the square brackets was sound, as Paul shows it is how to show a range of values to be tested, I just couldn't get it to work with # placeholders. Paul's is better as well in many ways, yours and mine would allow 99/99/2015, Paul's won't. His tests structure and and some value validity, although I think it could be tighter


Private Sub CDateAus_Click(DateEntry)
Dim DateEntry As String
Dim CDateAus As Date
Dim RegEx As Object, match As Object

CDateAus = Date
DateEntry = InputBox("Please enter an alternative date in dd/mm/yy format", "Date Entry")

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[\d]+[/][\d]+[/](19|20)[0-9]{2}"
RegEx.Global = True

For Each match In RegEx.Execute(DateEntry)


If IsDate(match.Value) Then

Exit For
End If
Next

If Not IsDate(DateEntry) Then

MsgBox "Date format not accepted. Please enter as dd/mm/yy or dd/mm/yyyy"
Else

MsgBox "Date format accepted!!! "
End If

Set RegEx = Nothing
End Sub

The Or did not work because you are Not'ing the test. When you or them, even if the test value is of one of the formats and the Not gives False, the test value will fail another test and the Not will give True, so you always get the message. To get the message, all of them should be not of the format, so and.

[Paul_Hossler]

Another idea

IMHO it has some advantages you might want to consider

1. It is more flexible for the user since they do not worry about a format; if Excel thinks it's a Date, then it's good
2. You can add 'too old' and 'too far' checking easily
3. It is Locale independent -- regardless of Region, a Date is a Date
4. I like to separate 'Data' from 'Presentation' so there is 3 options at the end; Short Date and Long Date use the computer settings for date format
5. The code is slightly more 'fall through' without any GoTo's





Option Explicit
Private Sub CDateAus_Click()
Dim DateEntry As Variant

Dim CDateAus As Date
CDateAus = Date

DateEntry = False
Do While VarType(DateEntry) = vbBoolean

DateEntry = Application.InputBox("Please enter an alternative date, or [Cancel] to exit", "Date Entry", Date, , , , , 1)

If VarType(DateEntry) = vbBoolean Then Exit Do

DateEntry = CDate(DateEntry)

If Date - DateEntry > 365 * 2 Then
Call MsgBox("Date is more than 2 years old. Try again", vbExclamation + vbOKOnly, "Date Entry")
DateEntry = False
ElseIf DateEntry - Date > 5 * 365 Then
Call MsgBox("Date is more than 5 years in the future. Try again", vbExclamation + vbOKOnly, "Date Entry")
DateEntry = False
Else
Exit Do
End If
Loop

If VarType(DateEntry) = vbBoolean Then
Call MsgBox("Date input was canceled by user", vbCritical + vbOKOnly, "Date Entry")
Exit Sub

Else
MsgBox "Opt 1 - Date format accepted!!! " & Format(DateEntry, "dd/mm/yyyy")

MsgBox "Opt 2 - Date format accepted!!! " & Format(DateEntry, "Short Date")

MsgBox "Opt 3 - Date format accepted!!! " & Format(DateEntry, "Long Date")

End If

End Sub



There are more elegant ways to do this (using On Error Goto To for example), but I'm a big fan of the KISS principle and this seemed 'Good 'Nuff' to me :yes