What is wrong with this if statement?

[Renee]

Good Morning all,

The selected biennium start date is 1/1/05
The selected biennium end date is 12/31/06

This is the If statement I am using on the Before Update event of the form:

If Not IsNull(Me.biennium) _
And (Me.Completion_Date.Value < Me.biennium.Column(1)) Then
'the Completion date is before the selected biennium starting date.
MsgBox "The course completion date is before " &
Me.biennium.Column(1) & ".", vbOKOnly
Cancel = True 'cancel the update event
Else
If Not IsNull(Me.biennium) _
And (Me.Completion_Date.Value > Me.biennium.Column(2)) Then
'the completion date is after the selected biennium ending date.
MsgBox "The course completion date is after " &
Me.biennium.Column(2) & ".", vbOKOnly
Cancel = True 'cancel the update event
End If
End If

When I enter a completion date of 7/20/05, the first statement returns true.
The message is showing the correct starting date of 1/1/05, so I know the
column reference is correct. When I switch the > & < just to test the
possiblities, then the second statement returns true stating : The completion
date (of 7/20/05) is after 12/31/05.

How do I correct this if statement?
Thank you much,
Renee

 

[Ken Snell [MVP]]

Chances are that the value in Me.biennium.Column(1) is being "seen" as a
text string...not uncommon for combo box's row source fields. Try casting it
as a date, and also split the If statement so that you won't get errors if
the combo box is indeed Null:


If Not IsNull(Me.biennium.Value) Then
If Me.Completion_Date.Value < CDate(Me.biennium.Column(1)) Then
'the Completion date is before the selected biennium starting date.
MsgBox "The course completion date is before " & _
Me.biennium.Column(1) & ".", vbOKOnly
Cancel = True 'cancel the update event
ElseIf Me.Completion_Date.Value > CDate(Me.biennium.Column(2)) Then
'the completion date is after the selected biennium ending date.
MsgBox "The course completion date is after " & _
Me.biennium.Column(2) & ".", vbOKOnly
Cancel = True 'cancel the update event
End If
End If

 

[Renee]

Perfect, thank you Ken!

Chances are that the value in Me.biennium.Column(1) is being "seen" as a
text string...not uncommon for combo box's row source fields. Try casting it
as a date, and also split the If statement so that you won't get errors if
the combo box is indeed Null:


If Not IsNull(Me.biennium.Value) Then
If Me.Completion_Date.Value < CDate(Me.biennium.Column(1)) Then
'the Completion date is before the selected biennium starting date.
MsgBox "The course completion date is before " & _
Me.biennium.Column(1) & ".", vbOKOnly
Cancel = True 'cancel the update event
ElseIf Me.Completion_Date.Value > CDate(Me.biennium.Column(2)) Then
'the completion date is after the selected biennium ending date.
MsgBox "The course completion date is after " & _
Me.biennium.Column(2) & ".", vbOKOnly
Cancel = True 'cancel the update event
End If
End If