Date mismatch ...

[adambc]

In an earlier post, you helped me format the output of a (US) date picker into UK format using ...

Private Sub cmdNextActionDate_Click()

Dim dateVariable As Date
dateVariable = CalendarForm.GetDate

If dateVariable <> 0 Then
    Me.txtNextActionDate.Text = Format(CLng(dateVariable), "dd/mm/yyyy")
End If

End Sub

At that time I was using the date picker to select 2 dates and testing that one was after the other when the user tried to save the userform ...

This time I am setting one date field using ...

txtReportedDate.value = Format(Date, "dd/mm/yyyy")

But when I test as follows ...

If txtNextActionDate < txtReportedDate Then
    MsgBox "Next Action Date must be after the Reported Date (today), please correct and Save again"
    Exit Sub
End If

Unless the dd value (irrespective of mm) of NextActionDate is >= to the dd value of ReportedDate my MsgBox appears ...

Any ideas please?

 

[Akuini]

Try using CDate:

If CDate(txtNextActionDate) < CDate(txtReportedDate) Then
    MsgBox "Next Action Date must be after the Reported Date (today), please correct and Save again"
    Exit Sub
End If

 

[adambc]

Not sure what CDATE is (will look it up) but it worked a treat, thanks ...

 

[adambc]

A supplementary question if I may (but tell me if I need to ask it in a new post) ...

CDATE works great, but doesn't like it if txtNextActionDate is blank (which is possible!) so that stops me using ...

If txtNextActionDate.value <> "" And CDate(txtNextActionDate) < CDate(txtReportedDate) Then
    MsgBox "Next Action Date must be after the Reported Date (today), please correct and Save again"
    Exit Sub
End If

Is there an easy way round this eg can I test if txtNextActionDate.value = "" and if it is, exit (and carry on with the rest of the Sub cmdSave_Click() routine without even trying to test CDate(txtNextActionDate) < CDate(txtReportedDate)???

Thanks ...

 

[jim may]

Something like...

If txtNextActionDate.value <> "" And CDate(txtNextActionDate) < CDate(txtReportedDate) Then
MsgBox "Next Action Date must be after the Reported Date (today), please correct and Save again"
Exit Sub
Else
Exit Sub
End If

 

[Akuini]

can I test if txtNextActionDate.value = "" and if it is, exit (and carry on with the rest of the Sub cmdSave_Click() routine

I don't understand, where is the code located? in Sub cmdSave_Click()?
So you don't mean exit sub? just continue to the next line?

 

[adambc]

Akuini ... yes, continue to next line without testing CDate(txtNextActionDate) < CDate(txtReportedDate) (which I know will fail with an error if txtNextActionDate.value = "

jim may ... as above, I don't want to Exit Sub, but even if I did if txtNextActionDate.value <> "", CDate(txtNextActionDate) < CDate(txtReportedDate) fails with an error (it clearly doesn't like null values!!!

 

[Akuini]

Maybe this:

If txtNextActionDate.Value <> "" Then
    If CDate(txtNextActionDate) < CDate(txtReportedDate) Then
       MsgBox "Next Action Date must be after the Reported Date (today), please correct and Save again"
       Exit Sub
    End If
End If

 

[adambc]

That's the way to do it!!! MANY THANKS

PS; you don't have a similarly straight forward way of solving the other post I put up earlier do you? (Using a working VLOOKUP/MATCH statement in a UserForm)

Thanks again ...

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.