A table validation rule question.

[Daniel]

I'm triying to replicate a sample database "Contact Tracking.accdb" that is
part of the companion CD from the book "Access 2007 Inside out" by John L.
Viescas and Jeff Conrad, but when i try to set the validation rule for the
Products table following the book instructions (Pages 175-178), Access 2007
complaints with a sintaxis error message. What i am triying to do is to use
the flow control function Inmediate If (IIF) in the validation rule. I have
been told the Flow Control group of functions -among others- can't be used to
make expressions for table or field validation rules in table design view.
However when i load the sample database from the companion CD it really works
as intended, i.e it uses IIF for a table validation rule and it works
perfectly well.
Can someone tell me how can it be done?
Where are the missing functions and how can i make them appear in the
expression builder built-in funtions group in table design view?
Thanks for any help.

 

[Ken Snell \(MVP\)]

Those pages refer to setting a validation rule for the entire table; is that
what you're seeking to do?

Post the expression that you've tried to use, but about which ACCESS 2007 is
complaining.

 

[Daniel]

Yes, that's exactly what i am trying to do.
The rule is IIf([TrialVersion]=True,[TrialExpire]>=30,[TrialExpire]=0) as
shown on page 177 of the book.
The problem is that i can't find IIf in the built-in functions group because
the Program Flow category of functions -among others- is missing in the
expression builder, and if i try to write the expression by hand then access
complaints about it. Incidentally i can't set the field validation rule
In(0,30,60,90) for the TrialExpire field either for the exact same reason.
As i said in my first post, i have been told that some categories of
built-in functions can't be used to build expressions for tables -they can be
used in forms and reports, though-, but when i load the sample database from
the companion cd both expressions work perfectly well.

How the book's authors did the trick?

Is there a way to force access to allow the use of those "Forbidden"
categories of built-in functions in table desing and not only on forms and
reports?

Are my questions a complete nonsense?

I am relatively new to access and database design in general so i need some
help to understand what's going on here.
By the way, i am using Windows XP SP2 and Office 2007 SP1 both in spanish.

P.S:sorry for my english.

 

[Ken Snell \(MVP\)]

I cannot answer why you don't see the IIf function in the Expression Builder
(I just loaded ACCESS 2007, and see what you see... rather, don't see what
you don't see, too). But, I had no problem typing an IIf function into the
Validation Rule property for a table in ACCESS 2007.

Are you sure you're trying to put the validation rule in the table's
Validation Rule property, and not the Validation Rule property for one of
the fields?

I will forward your post to one of the authors of the book to see if he can
provide additional assistance.

--

Ken Snell
<MS ACCESS MVP>

Yes, that's exactly what i am trying to do.
The rule is IIf([TrialVersion]=True,[TrialExpire]>=30,[TrialExpire]=0) as
shown on page 177 of the book.
The problem is that i can't find IIf in the built-in functions group
because
the Program Flow category of functions -among others- is missing in the
expression builder, and if i try to write the expression by hand then
access
complaints about it. Incidentally i can't set the field validation rule
In(0,30,60,90) for the TrialExpire field either for the exact same reason.
As i said in my first post, i have been told that some categories of
built-in functions can't be used to build expressions for tables -they can
be
used in forms and reports, though-, but when i load the sample database
from
the companion cd both expressions work perfectly well.

How the book's authors did the trick?

Is there a way to force access to allow the use of those "Forbidden"
categories of built-in functions in table desing and not only on forms and
reports?

Are my questions a complete nonsense?

I am relatively new to access and database design in general so i need
some
help to understand what's going on here.
By the way, i am using Windows XP SP2 and Office 2007 SP1 both in spanish.

P.S:sorry for my english.

Those pages refer to setting a validation rule for the entire table; is
that
what you're seeking to do?

Post the expression that you've tried to use, but about which ACCESS 2007
is
complaining.

 

[Daniel]

I am pretty sure about what i am doing and i can tell you that IIf can't be
used in the table validation rule property. I even loaded the sample database
and tried to edit the property -deleting the closing parenthesis of the
validation rule expression and then putting it back again so the expression
is left unchanged- to force access to parse the expression and as soon as i
try to move out of the property box access start complaining again.
By the way, i already made contact with Jeff Conrad, one of the book's
authors through the "Access Junkie" site and he told me maybe the issue has
to do with the fact I am using the spanish version of access.

I cannot answer why you don't see the IIf function in the Expression Builder
(I just loaded ACCESS 2007, and see what you see... rather, don't see what
you don't see, too). But, I had no problem typing an IIf function into the
Validation Rule property for a table in ACCESS 2007.

Are you sure you're trying to put the validation rule in the table's
Validation Rule property, and not the Validation Rule property for one of
the fields?

I will forward your post to one of the authors of the book to see if he can
provide additional assistance.

--

Ken Snell
<MS ACCESS MVP>

Yes, that's exactly what i am trying to do.
The rule is IIf([TrialVersion]=True,[TrialExpire]>=30,[TrialExpire]=0) as
shown on page 177 of the book.
The problem is that i can't find IIf in the built-in functions group
because
the Program Flow category of functions -among others- is missing in the
expression builder, and if i try to write the expression by hand then
access
complaints about it. Incidentally i can't set the field validation rule
In(0,30,60,90) for the TrialExpire field either for the exact same reason.
As i said in my first post, i have been told that some categories of
built-in functions can't be used to build expressions for tables -they can
be
used in forms and reports, though-, but when i load the sample database
from
the companion cd both expressions work perfectly well.

How the book's authors did the trick?

Is there a way to force access to allow the use of those "Forbidden"
categories of built-in functions in table desing and not only on forms and
reports?

Are my questions a complete nonsense?

I am relatively new to access and database design in general so i need
some
help to understand what's going on here.
By the way, i am using Windows XP SP2 and Office 2007 SP1 both in spanish.

P.S:sorry for my english.

Those pages refer to setting a validation rule for the entire table; is
that
what you're seeking to do?

Post the expression that you've tried to use, but about which ACCESS 2007
is
complaining.

--

Ken Snell
<MS ACCESS MVP>


I'm triying to replicate a sample database "Contact Tracking.accdb"
that
is
part of the companion CD from the book "Access 2007 Inside out" by John
L.
Viescas and Jeff Conrad, but when i try to set the validation rule for
the
Products table following the book instructions (Pages 175-178), Access
2007
complaints with a sintaxis error message. What i am triying to do is to
use
the flow control function Inmediate If (IIF) in the validation rule. I
have
been told the Flow Control group of functions -among others- can't be
used
to
make expressions for table or field validation rules in table design
view.
However when i load the sample database from the companion CD it really
works
as intended, i.e it uses IIF for a table validation rule and it works
perfectly well.
Can someone tell me how can it be done?
Where are the missing functions and how can i make them appear in the
expression builder built-in funtions group in table design view?
Thanks for any help.

 

[Ken Snell \(MVP\)]

Yes, I have heard that there may be a problem with the Spanish version's
Expression service, which is what "interprets" the expression. So the
problem likely is a bug in your version of ACCESS.

 

[Daniel]

Thank you for this information, let's see if i can do something about it.