Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Multiplying a field by a constant
- Thread starter Keidran
- Start date
Did you try change the field expression to:
[# of Photos] * 0.5
Duane
Hook'D on Access
MS Access MVP
[# of Photos] * 0.5
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #3
I have and the error I get is as follows:
The database engine does not recognize either the feild '# of Photos' in a validation expresion, or the default value in the table 'Photo Request'.
That is what i get when I put that expression in Default Value. If I put it in Validation Rule I get:
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint.
I have also tried renaming [# of Photos] and making another field [Time] with the default value of (0.5).
The database engine does not recognize either the feild '# of Photos' in a validation expresion, or the default value in the table 'Photo Request'.
That is what i get when I put that expression in Default Value. If I put it in Validation Rule I get:
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint.
I have also tried renaming [# of Photos] and making another field [Time] with the default value of (0.5).
Is "# of Photos" the actual field name or a caption? You need to use a true field name. You shouldn't have fields with symbols or spaces in the name. Find a good naming convention and stick to it.
Time is not a good name for a field since it is the name of a function. The field has to be storing something that can be described better with a quality field name.
Duane
Hook'D on Access
MS Access MVP
Time is not a good name for a field since it is the name of a function. The field has to be storing something that can be described better with a quality field name.
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #5
[# of Photos] is the field name and the caption. I have tried to change it to Photos with the same results.
I also realize [Time] is a poor choice of name for a field when its only value was going to be (0.5), I was just trying something that may have worked, it didn't but I wanted to try something.
I also realize [Time] is a poor choice of name for a field when its only value was going to be (0.5), I was just trying something that may have worked, it didn't but I wanted to try something.
Did you check your table design for the validation expression on the [Photo Request] field? That's what your current error message seems to be pointing to.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #7
This may sound a little juvenile, but can you be more specific as to what you mean? [Photo Request] is the name of the Table in question, the fields in question are [Photos] (formerly [# of Photos]) and [ManHours] (where I am trying to get [Photos]*0.5).
Ok,
[ol]
[li]open [Photo Request] in design view.[/li]
[li]view the table properties.[/li]
[li]find and either delete or change the Validation Rule.[/li]
[li]close and save your table design.[/li]
[/ol]
You should not be saving a value that can easily be calculated in a query. Storing this type of value is considered bad practice.
BTW: thanks for typing [] around your object names. It makes troubleshooting difficult when there are spaces in names and we don't know where one field ends and another starts ;-)
Duane
Hook'D on Access
MS Access MVP
[ol]
[li]open [Photo Request] in design view.[/li]
[li]view the table properties.[/li]
[li]find and either delete or change the Validation Rule.[/li]
[li]close and save your table design.[/li]
[/ol]
You should not be saving a value that can easily be calculated in a query. Storing this type of value is considered bad practice.
BTW: thanks for typing [] around your object names. It makes troubleshooting difficult when there are spaces in names and we don't know where one field ends and another starts ;-)
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #9
I have been able to get this to be calculated in a query, I also have had no luck in getting it to populate on my table.
I changed my validation rule to: [ManHours]=[Photos]*0.5
It accepts the change but does not auto populate. If I try to add the expression to [ManHours] default value or validation rule I get the same errors.
I changed my validation rule to: [ManHours]=[Photos]*0.5
It accepts the change but does not auto populate. If I try to add the expression to [ManHours] default value or validation rule I get the same errors.
I'm suprised that Access would accept that as a validation rule for the table but, even if it does, that is a validation rule. It does not change the values in the table (i.e. populate them), it only checks that the value provided matches the rule.
The only thing that you can get to "autopopulate" is a default value that is not dependent on fields in a table. The proper place to do calculations is in a query. Many programmers have a rule that customers NEVER work with base tables. They always see data presented by a query that may include more than one table, calculated fields, etc.
The only thing that you can get to "autopopulate" is a default value that is not dependent on fields in a table. The proper place to do calculations is in a query. Many programmers have a rule that customers NEVER work with base tables. They always see data presented by a query that may include more than one table, calculated fields, etc.
The validation rule for a table can contain an expression but it will be evaluated as either True or False. I doubt you could enter any records into the table where
[tt][blue]
[ManHours]<>[Photos]*0.5
[/blue][/tt]
If you had an un-normalized table tracking time at work you could have a Validation Rule like:
[tt][blue]
([PctBillable]+[PctNonBillable])= 1
[/blue][/tt]
Or perhaps
[tt][blue]
([BillableHrs]+[NonBillableHrs])= [TotalHrs]
[/blue][/tt]
Duane
Hook'D on Access
MS Access MVP
[tt][blue]
[ManHours]<>[Photos]*0.5
[/blue][/tt]
If you had an un-normalized table tracking time at work you could have a Validation Rule like:
[tt][blue]
([PctBillable]+[PctNonBillable])= 1
[/blue][/tt]
Or perhaps
[tt][blue]
([BillableHrs]+[NonBillableHrs])= [TotalHrs]
[/blue][/tt]
Duane
Hook'D on Access
MS Access MVP
- Status
Similar threads
- Locked
- Question