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:
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
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
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
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
Try this
gowflow
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
gowflow
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
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.
The application will be run only in CAN.
Hi Martin Liss,
I will give it a try and let you know the result.
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"
IsDate() will return True for both "15/01/2017" and "01/15/2017"
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.
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.
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
Still doing some test. will be back shortly will final results. gowflow, i'm also testing your code.
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?
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.
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... :)
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... :)
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.
Thanks again for your help. really appreciate.
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.