Check date 1 is before date 2 ...

[adambc]

I have a UserForm that creates a new record - which calls another UserFDorm (CalendarForm - Developed by Trevor Eyre) as follows ...

Function GetFormattedDate() As String
Dim dateVariable As Date
dateVariable = CalendarForm.GetDate

If dateVariable <> 0 Then
GetFormattedDate = Format$(dateVariable, "dd/mm/yyyy")
End If
End Function

Note: CalendarForm returns the date in US format - hence the Format command to turn it into UK format ...

Which is followed by ...

Private Sub cmdReportedDate_Click()

Me.txtReportedDate.Text = GetFormattedDate()

End Sub
Private Sub cmdIncidentDate_Click()

Me.txtIncidentDate.Text = GetFormattedDate()

End Sub

Note: both work as intended/expected ...

And then when the Save button is clicked ...

If txtReportedDate < txtIncidentDate Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

But unless both dates are in the same month (at least I think that's the trigger), the validation fails ...

I can't see why - any ideas???

Thanks ...

 

[adambc]

ERRATUM ...

And then when the Save button is clicked ...

Today = Format$(Date, "dd/mm/yyyy")

If txtIncidentDate > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If txtReportedDate < txtIncidentDate Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

 

[Joe4]

Note: CalendarForm returns the date in US format - hence the Format command to turn it into UK format ...

The issue is that the FORMAT function returns a text value, not a date one.
So you are comparing two text strings, not two dates (text strings will follow alphabetic sorting rules).

You need to either change those values back to dates for purposes of doing the comparison, or choose a format in which the alphabetic sorting rules would also match date sorting rules, i.e.
choosing a date format like "yyyy/mm/dd".

 

[adambc]

The issue is that the FORMAT function returns a text value, not a date one.
So you are comparing two text strings, not two dates (text strings will follow alphabetic sorting rules).

You need to either change those values back to dates for purposes of doing the comparison, or choose a format in which the alphabetic sorting rules would also match date sorting rules, i.e.
choosing a date format like "yyyy/mm/dd".

@Joe4

Thanks for your speedy reply ...

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...

Today = Format$(Date, "dd/mm/yyyy")

If Format$(txtIncidentDate, "dd/mm/yyyy") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "dd/mm/yyyy") < Format$(txtIncidentDate, "dd/mm/yyyy") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

 

[Joe4]

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...

I don't think you undestand what I am telling you.
The FORMAT$ function ALWAYS returns text values, not date values!

So, if you use it in comparisons like you are trying to do, you are going to get unexpected results, because text comparisons follow alphabetic rules.
A value like "01/01/2020" will be less considered than "12/31/1999" because when you look at the first characters of each, "0" comes before "1".

If you wanted to keep using the FORMAT$ function in your code, then you could get it to work right ONLY if you use a format like "yyyy/mm/dd" (where year is always first, then month, and days last), as that will make the dates look like "2020/01/01" and "1999/12/31", and those will sort the way you want.

Otherwise, you will need to convert the values you want to compare in your expressions to dates, by using something like the CDATE function (MS Excel: How to use the CDATE Function (VBA)).

 

[adambc]

@Joe4

Thanks for your speedy reply ...

So could I rewrite the validation (I must have the dd/mm/yyyy format on the UserForm and in the underlying table)? ...

Today = Format$(Date, "dd/mm/yyyy")

If Format$(txtIncidentDate, "dd/mm/yyyy") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "dd/mm/yyyy") < Format$(txtIncidentDate, "dd/mm/yyyy") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

IGNORE MY PREVIOUS REPLY ...

This seems to work ie changing format to yyyy/mm/dd for the purpose of validation, but keeping dd/mm/yyyy for the UserForm/underlying table ...

Or is their a better way???

Today = Format$(Date, "yyyy/mm/dd")

If Format$(txtIncidentDate, "yyyy/mm/dd") > Today Then
    MsgBox "The Incident Date must be today or earlier, please correct and Save again"
    Exit Sub
End If

If Format$(txtReportedDate, "yyyy/mm/dd") < Format$(txtIncidentDate, "yyyy/mm/dd") Then
    MsgBox "The Reported Date must be on or after the Incident Date, please correct and Save again"
    Exit Sub
End If

 

[Joe4]

This seems to work ie changing format to yyyy/mm/dd for the purpose of validation, but keeping dd/mm/yyyy for the UserForm/underlying table ...

Or is their a better way???

As I mentioned in my previous post, you can do either (the way you did it or by converting the values back to dates for comparison purposes).
Because VBA uses American date formats, if you are in the UK, I think the way you did it may be a bit more reliable, as I am not sure if doing a date conversion will always work properly with the competing US/UK formats.

Just be sure if you use the FORMAT$ function with the a date format, always use "yyyy/mm/dd".
It is important to have the four digit year, two digit month, and 2 digit day to make sure sorting works properly.
For example, if you used 2 digit years, "20/01/01" will come before "99/01/01", which is not correct for your comparisions.

 

[adambc]

@Joe4

Many thanks again - yet again Mr Excel contributors come to my rescue!!!

 

[Joe4]

You are welcome.