Validation syntax problem

[Stuart Green]

I have a simple form with 2 date fields on it, date1 and date2. Date1 is required but date2 is not. When input, date2 should be greater or the same as date1. In the properties of date2 I have put >=[date1] as the validation rule which seems to work OK. Problem is that I only want this to work when someone puts data in field2. it is OK for them to create the record with date2 blank. If they modify a record and take out data in field2 that is also OK and should not be validated, but I am damned if I can get the syntax to work. Ihave tried so many "ifs" that my rule is now very "iffy"!

 

[TessB]

I am thinking that I wouldn't put it in the validation field, but in the afterUpdate event of date2 (and actually add something for date1 as well.)

Lemme esplain... no.. that is too long... lemme sum up.

In the afterupdate event of date2 I would say,

If date2 is not null or date2 <> ''" then
if date2 < date1 then
Msgbox(Date2 needs to be greater... yadda yadda, vbokonly, Error!)
date2 = ""
end if
end if



I would also put a similar code in date1 in case the user initally enters a date larger than date1 in the date2 textbox, and then goes back and changes the date in date1. (Must plan ahead!)

 

[Stuart Green]

Thanks for the prompt reply. Well you have made me dip my toe in VB. I understand the logic behind your suggestion but when I tried to make it work I kept getting a can't find object type error. (I'm not very conversant with the syntax and when to use brackets etc).

 

[gurdiga]

Try to put this in validation rule:

iif(date2 Is Not Null,date1<date2,true)

 

[Stuart Green]

Hi gurdiga, tried that and still no joy. I went to a record where both date1 and date2 had been filled in and I deleted the contents of date2, (which could happen on this system) but it would not let me passed the validation

 

[indesisiv]

Very similar syntax to what TessB suggests with mostly the same code but it may solve you problem.

Try this.
Private Sub date1_AfterUpdate()
If Me.date2 <> Null Or Me.date2 <> "" Then
If Me.date2 < Me.date1 Then
'Msgbox "Date2 needs to be greater... yadda yadda", vbokonly, "Error!"
MsgBox "wrong", vbOKOnly
Me.date2 = ""
End If
Else
End If
End Sub

 

[indesisiv]

Sorry forgot to take out the test things I had in there ,...
THis is what i meant.
Private Sub date1_AfterUpdate()
If Me.date2 <> Null Or Me.date2 <> "" Then
If Me.date2 < Me.date1 Then
Msgbox "Date2 needs to be greater... yadda yadda", vbokonly, "Error!"
Me.date2 = ""
End If
Else
End If
End Sub

Sorry about that

 

[Pat Hartman]

ndesisiv and Tess,
The expression:
If Me.date2 <> Null Or Me.date2 <> ""
Is ALWAYS true because nothing can be both things at once.

In the BeforeUpdate event of Date1:

If IsNull(Date2) Then
Else
    If Date1 > Date2 Then
        MsgBox "Date1 may not be greater than Date2",vbOKOnly
        Cancel = True
        Me.Date1.Undo
    End If
End If

In the BeforeUpdate event of Date2:

If Date1 > Date2 Then
MsgBox "Date1 may not be greater than Date2", vbOKOnly
Cancel = True
End If

 

[Stuart Green]

Many thanks for all the help, Pat cracked it! Oh for my easy days with DataEase.

 

[TessB]

Pat is right again, as usual.

Pat, sometimes I have trouble with Null and "" (empty string.) Could you please explain to me when to use either? And does a Date field have any specific rule concerning Null or ""? I don't have a specific example to share, but there have been times where I think I have used Null improperly and do not know why. A brief explanation of the rules would be extremely helpful to me.

Much appreciation,
Tess

 

[Pat Hartman]

Text columns may be null or contain zero length strings - "". Numeric columns may be null but since they cannot hold text, they cannot contain zero length strings. Date/Time is a numeric data type. Text and Memo are the only Access text data types. Date/time data types are "special" since the integer and decimal components have separate uses. Help explains this and I have posted MANY explainations here.

The biggest mistake people make regarding nulls is failing to realize that the ONLY way to obtain a True result from a conditional expression that involves a field that contains nulls is to specifically test for null. Use the IsNull() function in VBA and use the Is Null operator in SQL.

A common error:
Where MyCode <> "A"
Will NOT return any rows where MyCode is null even though null is obviously not equal to "A".