• Validation in field in table (Access97 & XP)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Validation in field in table (Access97 & XP)

    Author
    Topic
    #380643

    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

    Viewing 2 reply threads
    Author
    Replies
    • #638162

      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?

    • #638217

      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.

    • #638231

      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.

      • #638340

        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.

        • #638342

          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*”.

          • #638345

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

    Viewing 2 reply threads
    Reply To: Validation in field in table (Access97 & XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: