Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation in field in table (Access97 & XP)

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Validation in field in table (Access97 & XP)

    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?
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation in field in table (Access97 & XP)

    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.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation in field in table (Access97 & XP)

    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.

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation in field in table (Access97 & XP)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation in field in table (Access97 & XP)

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

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation in field in table (Access97 & XP)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •