IIf Statement Validation Rules or Expression in Form

[mjhd]

Hey there,



After some lengthy investigating, I am unable to find a solution to my problem. I have a form where a user enters data in a field called [Value]. One of the fields is called [Product], it simply has a value of either Y or N. Based on this field, I would like the field [Value] to be either 0 or null and a message pops up if it isn't either one (if [Product] = N). If it is = Y, then it can be either 0 or null or any number. Thanks for nay help.

[pbaldy]

Personally I'd use the before update event of the form:

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

with the logical test adjusted to your needs.

[mjhd]

The statement I tried btw is:

=IIf([Product]="N",0 or is null, IIf[Product]="Y",0 or is null or [Value]...

The issue I'm having as well is that besides 0 or null when = Y, I would need the user's input to stand as well if they enter a number. I tried it in before update, did not give a suntax error but didn't do anything either.

[qa12dx]

=IIf([Product]="N",-1, IIf([Product]="Y",1,0))
what it's saying here is;
if prod =n then val=-1, else if prod=y then val=1, else val 0

suggestion, dont use NULL values. save urself lots of tears...

[mjhd]

=IIf([Product]="N",-1, IIf([Product]="Y",1,0))
what it's saying here is;
if prod =n then val=-1, else if prod=y then val=1, else val 0

suggestion, dont use NULL values. save urself lots of tears...

But I still need the user to be able to enter values in the [Value] field. They might enter 350, 1500, etc.

[qa12dx]

what u r saying is after the user puts in a value, if that value exists then the prod field will be y else n? for that you will have to run a "after update" event from that control box. after update use a dcount func. if the value is >0 then set the pord value

[mjhd]

what u r saying is after the user puts in a value, if that value exists then the prod field will be y else n? for that you will have to run a "after update" event from that control box. after update use a dcount func. if the value is >0 then set the pord value

I'm saying if the [Product] field is = Y, then allow the user to put in a #,0, or even leave null in the [Value]. If the [Product]=N, then the user can only put in a 0 or leave as null.

Alternatively, I could just have [Value] default to 0 if [Product]=N, and not allow any edits. But the user must still be able to edit the [Value] field if [Product]=Y.

Any ideas?