Comparing 2 dates from 2 fields in a form

[Swathi]

Hi everyone,

I am having a problem with the date comparision. Please help!!!..

I have 2 fields in a form called N_CREATION_DATE and N_APPROVAL_DATE. I attached a calendar to these fields so that the use can select a particular date from the calendar and once the date is selected, the selected date is entered into the fields. However, when I select the N_APPROVAL_DATE from the calender, I want to compare it with the N_CREATION_DATE, so that N_APPROVAL_DATE should be greater than or equal to the N_CREATION_DATE. I am trying to do this without using any command buttons for validation.The user should be able to get the error message when he enters a date before the N_CREATION_DATE into the N_APPROVAL_DATE field.

For example: If N_CREATION_DATE is 04/26/89 then my N_APPROVAL_DATE should be either 04/26/89 or 04/27/89 (the year,month and date should be greater than the date entered in N_CREATION_DATE) ..etc. But it should be values less than 04/26/89

Please help me through the steps..Here is the code for the calender that I used..

The commented lines are the code lines that I tried to use to compare the fields, but there was no luck.I am doing something wrong with the code and I am unable to think of what it is as I am new to VBA...Please help

------------------------------------------------------------------------------------------------------------------
Dim Originator As ComboBox
-------------------------------------------------------------------------------------------------------------------
Private Sub Calendar_Click()
Originator.Value = Calendar.Value
Originator.SetFocus
Calendar.Visible = False
Set Originator = Nothing
End Sub
-----------------------------------------------------------------------------------------------------------
Public Sub N_APPROVAL_DATE_Comb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = N_APPROVAL_DATE_Comb
Calendar.Visible = True
Calendar.SetFocus

'If (Me!N_CREATION_DATE_Comb > Me.N_APPROVAL_DATE_Comb) Then
If Not IsNull(N_APPROVAL_DATE_Comb) Then
Calendar.Value = N_APPROVAL_DATE_Comb.Value
Else
Calendar.Value = Date
' If Me!N_CREATION_DATE_Comb > Calendar.Value Then
' MsgBox "N_APPROVAL_DATE should be after NCR_CREATION_DATE. Invalid"
' Set Originator = N_APPROVAL_DATE_Comb
' Calendar.Visible = True
'Calendar.SetFocus
'Else
'Calendar.Value = Date
' MsgBox "Date entered is Valid"

'End If
End If
End Sub
------------------------------------------------------------------------------------------------------------------------
Public Sub N_CREATION_DATE_Comb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set Originator = N_CREATION_DATE_Comb
Calendar.Visible = True
Calendar.SetFocus

If Not IsNull(N_CREATION_DATE_Comb) Then
Calendar.Value = N_CREATION_DATE_Comb.Value
Else
Calendar.Value = Date
End If
End Sub

--------------------------------------------------------------------------------


Thanks,

 

[Swathi]

I got it working by using this approach..

I used the "LostFocus" Event procedure in the properties list of the N_APPROVAL_DATE combobox. I used this to validate my date once I move focus from my N_APPROVAL_DATE field to an other field.

Here is the link from where I go the help from:
http://www.profsr.com/vb/vbless09.htm

and below listed is the code that I used to do that.
-----------------------------------------------------------
Private Sub N_APPROVAL_DATE_Comb_LostFocus()
If N_CREATION_DATE_Comb.Value > N_APPROVAL_DATE_Comb.Value Then
MsgBox "Incorrect date entered"
Set Originator = N_APPROVAL_DATE_Comb
Calendar.Visible = True
Calendar.SetFocus
End If
End Sub
--------------------------------------------------------------

Thanks once again