We help IT Professionals succeed at work.

VB6 - Check if date format = "MM/DD/YYYY' and check if date also exist

Wilder1626
Wilder1626 asked
on
855 Views
Last Modified: 2017-04-29
Hi,
I need your help with below code. I must check all dates in MSHFlexgrid1 col (1) to see if the date is a real date with format "MM/DD/YYYY"

If the date don't exist, it will put in Col(0) the note: "BAD DATE FORMAT".

Example:
If it says in Col(1): 01/15/2017, January 15 2017 is a date that exist, so not problem.
If it says in Col(1): 15/01/2015, this is a bad date as there is no Month 15.

So far, i have this code:
Private Sub val_date_exist_Click()
    Dim strDate As String
    Dim dt As Date
    Dim I As Long
    For I = 1 To MSHFlexGrid1.Rows - 1

        strDate = Trim(MSHFlexGrid1.TextMatrix(I, 1))
        dt = DateSerial(CInt(Mid(strDate, 1, 2)), CInt(Mid(strDate, 4, 2)), CInt(Mid(strDate, 7, 4)))
        If strDate = Format(dt, "MM/DD/YYYY") Then

        Else
            MSHFlexGrid1.TextMatrix(I, 0) = MSHFlexGrid1.TextMatrix(I, 1) & " - " & "BAD DATE FORMAT"
        End If
    Next

    'Auto column fit
    Dim c As Long
    Dim cell_wid As Single
    Dim col_wid As Single
    Dim z As Long
    For c = 0 To MSHFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSHFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSHFlexGrid1.ColWidth(c) = col_wid + 120
    Next c

End Sub

Open in new window


But it always tell me that the date is bad even if it as the good format.

I must be missing something in the code.

Hope you will be able to help.

Thank you
Date-validation.zip
Comment
Watch Question

CERTIFIED EXPERT

Author

Commented:
I found a way to make it work but it's not very clean. I'm sure that we can do better then that.

Private Sub val_date_exist_Click()
    Dim i As Long
    Dim c As Long
    Dim d As Long
    Dim cell_wid As Single
    Dim col_wid As Single


    For d = 1 To MSHFlexGrid1.Rows - 1
        If MSHFlexGrid1.TextMatrix(kk, 1) <> "" Then

            txt = MSHFlexGrid1.TextMatrix(d, 1)
            If InStr(txt, "/") Then
                txt = Replace(txt, "/", "-")
                MSHFlexGrid1.TextMatrix(d, 1) = txt
            End If
        End If
    Next d

    For i = 1 To MSHFlexGrid1.Rows - 1


        If MSHFlexGrid1.TextMatrix(i, 1) = Format(CDate(MSHFlexGrid1.TextMatrix(i, 1)), "MM/dd/yyyy") Then

        Else
            MSHFlexGrid1.TextMatrix(i, 0) = Format(CDate(MSHFlexGrid1.TextMatrix(i, 1)), "MM/dd/yyyy") & " - " & "BAD DATE FORMAT"
        End If
    Next

    For d = 1 To MSHFlexGrid1.Rows - 1
        If MSHFlexGrid1.TextMatrix(d, 1) <> "" Then

            txt = MSHFlexGrid1.TextMatrix(d, 1)
            If InStr(txt, "-") Then
                txt = Replace(txt, "-", "/")
                MSHFlexGrid1.TextMatrix(d, 1) = txt
            End If
        End If
    Next d


    'Auto column fit
    For c = 0 To MSHFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSHFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSHFlexGrid1.ColWidth(c) = col_wid + 120
    Next c

End Sub

Open in new window

gowflowPartner
CERTIFIED EXPERT

Commented:
Try this

Private Sub val_date_exist_Click()
    Dim strDate As String
    Dim dt As Date
    Dim I As Long
    For I = 1 To MSHFlexGrid1.Rows - 1

        strDate = Trim(MSHFlexGrid1.TextMatrix(I, 1))
        If IsDate(strDate) Then
            dt = DateSerial(CInt(Mid(strDate, 1, 2)), CInt(Mid(strDate, 4, 2)), CInt(Mid(strDate, 7, 4)))
        'If strDate = Format(dt, "MM/DD/YYYY") Then
        Else
            MSHFlexGrid1.TextMatrix(I, 0) = MSHFlexGrid1.TextMatrix(I, 1) & " - " & "BAD DATE FORMAT"
        End If
    Next

    'Auto column fit
    Dim c As Long
    Dim cell_wid As Single
    Dim col_wid As Single
    Dim z As Long
    For c = 0 To MSHFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSHFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSHFlexGrid1.ColWidth(c) = col_wid + 120
    Next c

End Sub

Open in new window


gowflow
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Couldn't you just do this?
Private Sub val_date_exist_Click()
Dim lngRow As Long

For lngRow = 1 To MSHFlexGrid1.Rows - 1
    MSHFlexGrid1.TextMatrix(lngRow, 1) = Format(MSHFlexGrid1.TextMatrix(lngRow, 1), "MM/DD/YYYY")
    If Not IsDate(MSHFlexGrid1.TextMatrix(lngRow, 1)) Then
        MSHFlexGrid1.TextMatrix(lngRow, 0) = "BAD DATE FORMAT"
    End If
Next
End Sub

Open in new window

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Wilder1626

Does this application run in different countries or just in the United States?
CERTIFIED EXPERT

Author

Commented:
Hi aikimark
The application will be run only in  CAN.

Hi Martin Liss,
I will give it a try and let you know the result.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Since Canada commonly uses three different date formats, what makes one less 'wrong'?

IsDate() will return True for both "15/01/2017" and "01/15/2017"
CERTIFIED EXPERT

Author

Commented:
That is why wanted to force MM/DD/YYYY.

If 01/02/2017 could be good for Fev 1 2017 or Jan 02 2017, but if i have 25/01/2017, there is not month 25.

I'm just after a logic, not the accuracy really. The date just need to exist.

Hope this make sense.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Will the date always be 10 characters including two slashes, or could it be just 1/1 or just 23?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
gowflowPartner
CERTIFIED EXPERT

Commented:
@wilder1626
Did you try the code I posted ?

gowflow
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
This seems reliable.  Compare the result of two different date conversion routines.  If they are unequal, then you probably don't have a correct date.
strDate = MSHFlexGrid1.TextMatrix(lngRow, 1)
If CDate(strDate) <> DateSerial(Right(strDate, 4), Left(strDate, 2), Mid(strDate, 4, 2)) Then
    MSHFlexGrid1.TextMatrix(lngRow, 0) = "BAD DATE FORMAT"
End If

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
aikimark that fails on a valid date like 1/27/2017.
CERTIFIED EXPERT

Author

Commented:
Still doing some test. will be back shortly will final results. gowflow, i'm also testing your code.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
I thought the dates were in the MM/DD/YYYY or DD/MM/YYYY format.  Single digits were not specified in the question text.  Did I miss a Wilder comment about that?
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did I miss a Wilder comment about that?
No he didn't say one way or the other. He also didn't answer my
Will the date always be 10 characters including two slashes, or could it be just 1/1 or just 23?
question.
gowflowPartner
CERTIFIED EXPERT

Commented:
It seems Wilder ... is going Wild !!!! he is in testing mode so don't expect replies ... :)
gowflow
CERTIFIED EXPERT

Author

Commented:
Hi again

Martin Liss, yes, it will always be 10 characters. MM/DD/YYYY. Never single digit.

aikimark, i will also test ID: 42106162.

gowflow, lot of delays. so sorry. Yes, it's pretty wild today...  :)
Partner
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thanks a lot for your help. It took me a while to come to a conclusion but was able to try each code with  25 000 records in the grid.

Thanks again for your help. really appreciate.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Did you notice any performance differences?
CERTIFIED EXPERT

Author

Commented:
with 25 000 records, it took some time to run.

I should put a timer to see the performance. I will do it on Monday and let you know. small volume goes pretty quick no matter the code i take.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
My How to time code article may help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.