Question Validating Two Fields against each other PLEASE HELP!

[htmlasp]

Hi,

I am trying to validate two fields in a form against each other. Neither are required, but If one is filled in, then the other must be filled in. If neither is filled in then they can both be blank. It is for a time, and description related to that time.

So if they enter in 5 hours, they have to type in the description field describing those 5 hours. If they type in the description field, they have to type in the hours. Vice versa. Or they can both be blank.

Right now I put in the table properties validation rule
([Other Description In] Is Null) Or ([Other Time In]>0)

This works half way. It stops them from putting in a description without time. but they can still put time without a description.

I can't figure out how to do multiple validations of the same two fields. If I do an And or another Or statement it breaks the whole thing and they can enter with no restrictions.

Any advice?

Also, I tired to do it at form level for the other half (to stop them from putting time without a description) but it brings up a pop up and they cant tab to the description field to enter a description without putting time back to "0" first. Very stupid.

 

[pbaldy]

I would use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

With your logic of testing both fields.

 

[htmlasp]

I would use the before update event of the form:

With your logic of testing both fields.

Thank you for your advice, but I'm sorry I'm not sure I follow.

I don't know what to do. Can you show me an example?

 

[pbaldy]

An example of what? The basics are in the link. You would use an If/Then/Else block to apply your logic (more info in VBA help). In pseudo-code, you would be saying

If (FirstField is Null AND SecondField Is Null) OR (FirstField is Not Null AND SecondField Is Not Null) Then
  All is Well
Else
  Cancel and warn user
End If

 

[htmlasp]

Okay so I'm trying this...

If ([Other Description In] Is Null And [Other Time In] < 1) Or ([Other Description In] Is Not Null And [Other Time In] > 0) Then
MsgBox ("no problem")
Else
MsgBox ("problem")
End If

Just to test it with the message box responses.

Getting run time error 424 "object required"

 

[pbaldy]

Note that I said that was pseudo code, so I'd use the test from my link, which tests for both Null and a zero length string, which look the same to you and me but not Access.