View Full Version : If Not Like - Checking string for date format
reyreyreyes
03-23-2015, 02:02 AM
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
03-23-2015, 02:25 AM
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
03-23-2015, 09:27 AM
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
03-23-2015, 12:45 PM
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
03-23-2015, 02:29 PM
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
03-24-2015, 06:21 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.