How do I get the result of an expression in my form to return the.

[Guest]

Help! I have created a form to enter invoice information. The corresponding
Invoice table I have created has the same fields to hold this information.
The problem is with the text boxes in the form for 'Fee', 'IVA 16%', and
'Total Fee'. I have been able to get the 'Fee' box to return the exact value
entered in the form into the table (by naming the data type as 'text' in the
table design). Not sure if this is correct either. But the other two boxes
contain expressions which I've entered to calculate tax and total fee, these
two are not returning the resultant value to the corresponding fields in the
table. Both fields just show 0.00 € and it's driving me nuts!

 

[fredg]

Help! I have created a form to enter invoice information. The corresponding
Invoice table I have created has the same fields to hold this information.
The problem is with the text boxes in the form for 'Fee', 'IVA 16%', and
'Total Fee'. I have been able to get the 'Fee' box to return the exact value
entered in the form into the table (by naming the data type as 'text' in the
table design). Not sure if this is correct either. But the other two boxes
contain expressions which I've entered to calculate tax and total fee, these
two are not returning the resultant value to the corresponding fields in the
table. Both fields just show 0.00 ¤ and it's driving me nuts!

Well if Fee is supposed to be a number, why are you storing it as
text. The [Fee] field's datatype should be Currency.

The IVA16% and Total Fee fields should NOT be fields in your table.

There is no need to store calculated data. As long as you have stored
the values needed in the calculation, whenever you need the results,
calculate it, either on a form or in a report....
[TotalFee] = [Fee] * [TaxRate]

or in a query....
TotalFee:[Fee] * [TaxRate]

 

[Steve Schapel]

Jeni,

There seems to be a fundamental misunderstanding about the nature of the
database. A control on a form will communicate with a field in a table
if the control is "bound" to that field. You can see this from the
Control Source property of the control on the form, if you look at the
properties. It is not related to the name of the control. The control
can be named the same as the field it is bound to, or it can be named
something else. But apparently you have two controls on the form which
do a calculation based on existing values in other fields. The
calculation expression will be in the Control Source of these controls.
These controls are not bound to table fields, and the values shown in
them will not be entered in the table. Nor should they be... These
calculated values are redundant, and it is incorrect to store them in
the table. The easiest solution to your problem is to go to the table
design and delete the IVA 16% and Total Fee fields from the table.
Whenever you need these values, calculate them "on the fly" - that's the
"database way"! Calculate them in a query, or in the control source of
a form or report textbox.

By the way, as an aside, it is not a good idea to use a % as part of the
name of a field or control.

 

[Guest]

Hello fredg
Many thanks for your help. I'm a new user of Access and forgot that
databases should only hold data and not calculations. I also thought I could
create a form that would print as an Invoice. I realise now of course that I
need to create a report or query to achieve this. The reason that I changed
the 'Fee' field to a text field was because when I set it as currency, as it
should be, it rounded up the figure entered, eg 206.90 euros kept changing to
207 euros. How do I stop it doing that?

Jeni B
Spain

Help! I have created a form to enter invoice information. The corresponding
Invoice table I have created has the same fields to hold this information.
The problem is with the text boxes in the form for 'Fee', 'IVA 16%', and
'Total Fee'. I have been able to get the 'Fee' box to return the exact value
entered in the form into the table (by naming the data type as 'text' in the
table design). Not sure if this is correct either. But the other two boxes
contain expressions which I've entered to calculate tax and total fee, these
two are not returning the resultant value to the corresponding fields in the
table. Both fields just show 0.00 € and it's driving me nuts!

Well if Fee is supposed to be a number, why are you storing it as
text. The [Fee] field's datatype should be Currency.

The IVA16% and Total Fee fields should NOT be fields in your table.

There is no need to store calculated data. As long as you have stored
the values needed in the calculation, whenever you need the results,
calculate it, either on a form or in a report....
[TotalFee] = [Fee] * [TaxRate]

or in a query....
TotalFee:[Fee] * [TaxRate]

 

[Guest]

Steve,

Thanks for your explanation and you're absolutely right, I'm a new user and
my understanding of the logic of the database was faulty but the light had
begun to dawn! I realise now that I need to create an invoice that I can
print by using a query or a form. All your helpful points taken on board,
thanks again..

Jeni B
Spain

 

[fredg]

Hello fredg
Many thanks for your help. I'm a new user of Access and forgot that
databases should only hold data and not calculations. I also thought I could
create a form that would print as an Invoice. I realise now of course that I
need to create a report or query to achieve this. The reason that I changed
the 'Fee' field to a text field was because when I set it as currency, as it
should be, it rounded up the figure entered, eg 206.90 euros kept changing to
207 euros. How do I stop it doing that?

Jeni B
Spain

Help! I have created a form to enter invoice information. The corresponding
Invoice table I have created has the same fields to hold this information.
The problem is with the text boxes in the form for 'Fee', 'IVA 16%', and
'Total Fee'. I have been able to get the 'Fee' box to return the exact value
entered in the form into the table (by naming the data type as 'text' in the
table design). Not sure if this is correct either. But the other two boxes
contain expressions which I've entered to calculate tax and total fee, these
two are not returning the resultant value to the corresponding fields in the
table. Both fields just show 0.00 ¤ and it's driving me nuts!

Well if Fee is supposed to be a number, why are you storing it as
text. The [Fee] field's datatype should be Currency.

The IVA16% and Total Fee fields should NOT be fields in your table.

There is no need to store calculated data. As long as you have stored
the values needed in the calculation, whenever you need the results,
calculate it, either on a form or in a report....
[TotalFee] = [Fee] * [TaxRate]

or in a query....
TotalFee:[Fee] * [TaxRate]

There is a difference between a field's Format (i.e. Currency Format)
and a field's Datatype (i.e. Currency Datatype).
I suspect you set the field's format to currency, but the field's
datatype was Integer or Long Integer.
By definition, an Integer is a whole number and can not have a decimal
value.

Change the field's datatype to Currency.
Also set the field's Format property to Currency
Set the Field's Decimal Places to 2.

 

[onedaywhen]

The [Fee] field's datatype should be Currency.

That ain't necessarily so. In my databases, currency is the
three-letter ISO 4217 code
(http://en.wikipedia.org/wiki/ISO_currency_codes) and the corresponding
monetary amount is DECIMAL(20, 5) where four decimal places is required
and storing an additional significant figure allows for a custom
rounding algorithm, rather than the mandatory one exhibited by
Access/Jet's CURRENCY data type.

Jamie.

--

 

[Guest]

Thank you fredg
All sorted, you're a star!
Jeni

Hello fredg
Many thanks for your help. I'm a new user of Access and forgot that
databases should only hold data and not calculations. I also thought I could
create a form that would print as an Invoice. I realise now of course that I
need to create a report or query to achieve this. The reason that I changed
the 'Fee' field to a text field was because when I set it as currency, as it
should be, it rounded up the figure entered, eg 206.90 euros kept changing to
207 euros. How do I stop it doing that?

Jeni B
Spain

On Tue, 31 Oct 2006 08:56:02 -0800, Jeni B wrote:

Help! I have created a form to enter invoice information. The corresponding
Invoice table I have created has the same fields to hold this information.
The problem is with the text boxes in the form for 'Fee', 'IVA 16%', and
'Total Fee'. I have been able to get the 'Fee' box to return the exact value
entered in the form into the table (by naming the data type as 'text' in the
table design). Not sure if this is correct either. But the other two boxes
contain expressions which I've entered to calculate tax and total fee, these
two are not returning the resultant value to the corresponding fields in the
table. Both fields just show 0.00 € and it's driving me nuts!

Well if Fee is supposed to be a number, why are you storing it as
text. The [Fee] field's datatype should be Currency.

The IVA16% and Total Fee fields should NOT be fields in your table.

There is no need to store calculated data. As long as you have stored
the values needed in the calculation, whenever you need the results,
calculate it, either on a form or in a report....
[TotalFee] = [Fee] * [TaxRate]

or in a query....
TotalFee:[Fee] * [TaxRate]

There is a difference between a field's Format (i.e. Currency Format)
and a field's Datatype (i.e. Currency Datatype).
I suspect you set the field's format to currency, but the field's
datatype was Integer or Long Integer.
By definition, an Integer is a whole number and can not have a decimal
value.

Change the field's datatype to Currency.
Also set the field's Format property to Currency
Set the Field's Decimal Places to 2.