asked on 4/22/2017

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

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

Visual Basic Classic

Last Comment

Martin Liss

4/29/2017

Wilder1626

4/22/2017

ASKER

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

Jacques Geday

4/23/2017

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 Liss

4/23/2017

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

aikimark

4/23/2017

@Wilder1626

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

Wilder1626

4/23/2017

ASKER

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.

aikimark

4/23/2017

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"

Wilder1626

4/23/2017

ASKER

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 Liss

4/23/2017

Will the date always be 10 characters including two slashes, or could it be just 1/1 or just 23?

SOLUTION

Martin Liss

4/24/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jacques Geday

4/24/2017

@wilder1626
Did you try the code I posted ?

gowflow

aikimark

4/24/2017

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 Liss

4/24/2017

aikimark that fails on a valid date like 1/27/2017.

Wilder1626

4/24/2017

ASKER

Still doing some test. will be back shortly will final results. gowflow, i'm also testing your code.

aikimark

4/24/2017

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?

SOLUTION

aikimark

4/24/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Martin Liss

4/24/2017

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.

Jacques Geday

4/24/2017

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

Wilder1626

4/25/2017

ASKER

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... :)

ASKER CERTIFIED SOLUTION

Jacques Geday

4/25/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Wilder1626

4/29/2017

ASKER

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.

aikimark

4/29/2017

Did you notice any performance differences?

Wilder1626

4/29/2017

ASKER

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 Liss

4/29/2017

My How to time code article may help.

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

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