Comparing Multiple Dates

[adambc]

You are welcome.

@Joe4

Wondering if you can help me again?!!!

Now that I've got the basic validation working I need to use it - but against a number of dates, not just one ...

This code works perfectly for a single date ...

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

... but I also have Date2 ... Date6 and I want to test them once only ie not a separate test for each date ...

I've tried ...

If  (Format$(txtDate1, "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd") Or _
'    Format$(txtDate2, "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd") Or _
'    Format$(txtDate3, "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd") Or _
'    Format$(txtDate4 "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd") Or _
'    Format$(txtDate5, "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd") Or _
'    Format$(txtDate6, "yyyy/mm/dd") < Format$(txtReportedDate, "yyyy/mm/dd")) _
'    And ((Format$(txtDate1, "yyyy/mm/dd") <> "" Or Format$(txtDate2, "yyyy/mm/dd") <> "" Or _
'    Format$(txtDate3, "yyyy/mm/dd") <> "" Or Format$(txtDate4, "yyyy/mm/dd") <> "" Or _
'    Format$(txtDate5, "yyyy/mm/dd") <> "" Or Format$(txtDate6, "yyyy/mm/dd") <> "") Then
'    MsgBox "Dates must be on or after the Reported Date, please correct and Save again"
'    Exit Sub
'End If

... but get a syntax error that I can't work out ...

Can you help please?

Thanks ...

 

[dmt32]

Hi,
untested but try following & see if does what you want

Dim IsValidDateEntry As Boolean
    Dim i As Integer
    For i = 1 To 6
        With Me.Controls("txtDate" & i)
            If IsDate(.Value) Then
                IsValidDateEntry = DateValue(.Value) >= DateValue(Me.txtReportedDate.Value)
            Else
                IsValidDateEntry = False
            End If
        If Not IsValidDateEntry Then
            MsgBox "Dates must be on or after the Reported Date" & Chr(10) & _
                    "Please correct and Save again", 48, "Invalid Entry"
            .SetFocus
            Exit Sub
        End If
         End With
    Next i

solution uses DateValue Function that recognizes the order for month, day, and year according to the short date format that specified for your system.

Hope Helpful

Dave

 

[adambc]

Hi,
untested but try following & see if does what you want

Dim IsValidDateEntry As Boolean
    Dim i As Integer
    For i = 1 To 6
        With Me.Controls("txtDate" & i)
            If IsDate(.Value) Then
                IsValidDateEntry = DateValue(.Value) >= DateValue(Me.txtReportedDate.Value)
            Else
                IsValidDateEntry = False
            End If
        If Not IsValidDateEntry Then
            MsgBox "Dates must be on or after the Reported Date" & Chr(10) & _
                    "Please correct and Save again", 48, "Invalid Entry"
            .SetFocus
            Exit Sub
        End If
         End With
    Next i

solution uses DateValue Function that recognizes the order for month, day, and year according to the short date format that specified for your system.

Hope Helpful

Dave

Dave

Thanks but I may have misled you, apologies ...

Date1 ... 6 are not the real names - I had to use them to disguise some underlying information - their real format is txtXxxxxXxxxxDate where XxxxxXxxxx are unconnected naratives ...

Is there another approach that I can use?

Thanks ...

 

[dmt32]

Dave

Thanks but I may have misled you, apologies ...

Date1 ... 6 are not the real names - I had to use them to disguise some underlying information - their real format is txtXxxxxXxxxxDate where XxxxxXxxxx are unconnected naratives ...

Is there another approach that I can use?

Thanks ...

not sure why the sensitivity over a control name but solution should work if each control has a suffix (number) at the end or you can put all the control names in an array. If you want to share bit more info happy to help

Dave

 

[dmt32]

For using an array of control names in the code

Option Base 1

    Dim IsValidDateEntry As Boolean
    Dim i As Integer
    Dim ControlsArr As Variant
    
    ControlsArr = Array("txtDate1", "txtDate2", "txtDate3", _
                        "txtDate4", "txtDate5", "txtDate6")
    For i = 1 To 6
        With Me.Controls(ControlsArr(i))
            If IsDate(.Value) Then
                IsValidDateEntry = DateValue(.Value) >= DateValue(Me.txtReportedDate.Value)
            Else
                'ignore blank textboxes
                IsValidDateEntry = Len(.Value) = 0
            End If
        If Not IsValidDateEntry Then
            MsgBox "Dates must be on or after the Reported Date" & Chr(10) & _
                    "Please correct and Save again", 48, "Invalid Entry"
            .SetFocus
            Exit Sub
        End If
         End With
    Next i

Change the controls names shown in BOLD in the array as required

Note Option Base 1 statement which goes at very TOP of your forms code page OUTSIDE any procedure.

Dave

 

[Joe4]

Split from: Check date 1 is before date 2 ...

Since this really is a new question, I have moved it to its own thread (new questions should be posted to new threads, even if they are for the same project).
That will also allow you to acknowledge that solution to the new problem, as well as the original (you cannot mark two different posts as solutions in a single thread).

 

[adambc]

@Joe4 ... apologies, I should have known better and posted a new question (linked to the first one)!

@dmt32 ... apologies for the delay in replying - my question relates to the voluntary work I'm doing in "retirement" which only happens on Wednesdays!

Have adapted your code and it works a treat, thank you - it's also given me a great alternative approach to some other repeated sub routines elsewhere in the form! ...

One question though ...

My UserForm has 3 pages (effectively 3 Sections) - .SetFocus works if the focus is on page/Section 3 (where the date contriols appear) but if the focus is on another page/Section it fails - is there a further command that I can use to .SetFocus on another page/Section?

Many thanks ...

 

[dmt32]

One question though ...

My UserForm has 3 pages (effectively 3 Sections) - .SetFocus works if the focus is on page/Section 3 (where the date contriols appear) but if the focus is on another page/Section it fails - is there a further command that I can use to .SetFocus on another page/Section?

Many thanks ...

Have a look at my post here:Setfocus on textbox on multipage with a macro
and see if you can update to meet your project need.

Dave

 

[adambc]

Dave

I'd got to MultiPage1.Value=? before I saw your reply, but that gives me even more flex - many thanks again ...

Adam