Avatar of Wilder1626
Wilder1626Flag for Canada

asked on 

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

Avatar of undefined
Last Comment
Martin Liss
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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

Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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

Avatar of aikimark
aikimark
Flag of United States of America image

@Wilder1626

Does this application run in different countries or just in the United States?
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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.
Avatar of aikimark
aikimark
Flag of United States of America image

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"
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Will the date always be 10 characters including two slashes, or could it be just 1/1 or just 23?
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

@wilder1626
Did you try the code I posted ?

gowflow
Avatar of aikimark
aikimark
Flag of United States of America image

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

aikimark that fails on a valid date like 1/27/2017.
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

Still doing some test. will be back shortly will final results. gowflow, i'm also testing your code.
Avatar of aikimark
aikimark
Flag of United States of America image

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
Avatar of aikimark
aikimark
Flag of United States of America image

Blurred text
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.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

It seems Wilder ... is going Wild !!!! he is in testing mode so don't expect replies ... :)
gowflow
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Blurred text
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.
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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.
Avatar of aikimark
aikimark
Flag of United States of America image

Did you notice any performance differences?
Avatar of Wilder1626
Wilder1626
Flag of Canada image

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.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

My How to time code article may help.
Visual Basic Classic
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo