If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A table validation rule question.
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. |
#2
|
|||
|
|||
A table validation rule question.
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 "Daniel" wrote in message ... 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. |
#3
|
|||
|
|||
A table validation rule question.
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)" wrote: 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 "Daniel" wrote in message ... 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. |
#4
|
|||
|
|||
A table validation rule question.
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 "Daniel" wrote in message ... 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)" wrote: 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 "Daniel" wrote in message ... 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. |
#5
|
|||
|
|||
A table validation rule question.
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. "Ken Snell (MVP)" wrote: 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 "Daniel" wrote in message ... 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)" wrote: 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 "Daniel" wrote in message ... 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. |
#6
|
|||
|
|||
A table validation rule question.
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. -- Ken Snell MS ACCESS MVP "Daniel" wrote in message news 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. |
#7
|
|||
|
|||
A table validation rule question.
Thank you for this information, let's see if i can do something about it.
"Ken Snell (MVP)" wrote: 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. -- Ken Snell MS ACCESS MVP "Daniel" wrote in message news 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. |
Thread Tools | |
Display Modes | |
|
|