Validation Rule IIF condition doesnt work (1 Viewer)

Hi guys,

I have a Ms Access table which has fields
1. "DiskName"
2. "DiskType" (which is a LookUp field that has Two values "CD","DVD" to
choose from)
3. "Category"(Which is also a LookUp field that has Two values
"Single","Collection" to choose from)

What I need to be done is bellow.

IF (DiskType="CD") THEN
Can enter Only "Single" For Category Field

ELSE IF (DiskType="DVD") THEN
Can enter any of the two values ("Single" or "Collection")

The Equation I used in the Validation Rule field of "Category" field is mentioned bellow.

=IIF(DiskType="CD","Single","Collection")

But the above equation Results in
1. If DiskType is "CD" - You can enter Only value "Single" (Which is correct)
2. but If DiskType is "DVD" - You can enter Only value "Single"
(Which is NOT correct)

Can someone please help with the equation ???



Thank You.

regards,

Deelliiee

Where are you trying to do this. IMHO it is best to do this kind of data validation from within a form rather than a table.

The syntax for IIF is
Your code
will read as
IF the DiskType is "CD" THEN return "Single".
IF the DiskType is NOT "CD" THEN return "Collection".

From what you posted you are trying to do though, this is not relevant and would cause problems. You are trying to ONLY validate if the DiskType is "CD". This should be done on the form. After they have selected the DiskType you would want code that, in effect, says
This way if the user selects "CD" you know what the category will be AND the user won't be able to change it.

If you only have two options for "DiskType", and only one of the two actually uses "Category", you may not need "Category". If you will only ever have the two disk types then you really have three; "CD", "DVD Single", "DVD Collection". Unless you are planning to add more "DiskType" and "Category" values you may want to rework how you are saving this data.

IIf([disktype]="CD",[Category]="Single",IIf([disktype]="DVD",Trim([Category] & "")<>"",False))

I would use a table to solve the problem rather than code. The table would have three rows.

CD, Single
DVD, Single
DVD, Collection

Use a combo with the values CD or DVD for the first field. Then a query for the second fcombo that uses the above table with a where clause

Select Media, Group
From tblMedia
Where Media = Forms!yourform!cboMedia
Order by Group

In the AfterUpdate event of the Media combo, requery the group combo.

Me.cboGroup.Requery

This allows for easy expansion without requiring coding changes. In fact, you could give the user a way to update this table and they wouldn't have to even call you to make the change.