Validation in field in table (Access97 & XP)

Topic

New Reply

I have tried to use the following as Validation for a field,

IIF(like A*,[Des] not Null,IIf(Like B*,[Des] not null, IIF(like C*,[Des] not null,0)))
It will not work am I doing something wrong?

Greg

Reply | Quote

Replies

It is not clear what you are trying to do, so more information is needed:

Where and How are you trying to use that expression? Is it in the validation rule property of the field, or in an after update event of a form, or somewhere else.

Do you expect the expression you listed to tell you that there is a problem, or fix the problem?

The syntax for an IIF is : (test, valueif true, valueif false)
So in your expression “[Des] not null” is supposed to be the valueif true. I don’t know what this means.

When you use like you need this syntax : like “A” & “*”

What validation do you want this expression to achieve?

Reply | Quote

Are you trying to build a validation for a field in a table or something running in a form? Validations in table fields are fairly limited and generally cause more problems than they solve.

Reply | Quote

In Access 97 (I don’t have XP), the validation rule for a field in a table can’t refer to other fields. Record level validation rules for a table *can* refer to more than one field in that table. You set a record level validation rule in the Properties window for the table.

I suspect that the validation rule you are trying to set up is *not* for the field named Des. Therefore, it must be set at the table level. If I interpret your intention correctly, it could look like this:

[MyField] Like “[A-C]*” And [Des] Is Not Null

where MyField is the name of the field you were trying to set a validation rule for. This validation rule will accept a record if MyField starts with A, B or C AND Des is not empty.

But, as Charlotte wrote, validation rules in a table are limited; I would advice to use them only for simple rules like “>0”. If you make a mistake in a more complicated rule, there is no way to debug it. Validation rules for controls on forms are more flexible. But for complex validation, use the BeforeUpdate event of a control or of the form.

Reply | Quote

I was trying to validate from a field in a table but I can do this validation from a from……
To tell you more about the validation ……I need to make sure if a code is A*(EX….A01,A02,A03) or B* or C* that the field
called [Des] has a number put in it…..but if the code is D or E, J or anything else then dont worry about a [Des] having a number put in it.

Reply | Quote

Try this as validation rule at the table level (in the Properties window of the table):

Not ([MyField] Like “[A-C]*” And [Des] Is Null)

where MyField must be replaced by the name of the field containing the code. Explanation: you want to prevent the situation where the code starts with C AND the Des field is empty. Like “[A-C]*” is a handy shortcut for Like “A*” Or Like “B*” Or Like “C*”.

Reply | Quote

Thanks Hans will try it Monday when I am back at work and let you know If it worked for me!

Reply | Quote