Calculate Button

[Jose Aleman]

Hi,

I wanted to create a calculate button.

My fields

Field Name Data Type Length
Broker Number Double
Credit Number Double
Application Number Double
Refund Number Double

Refund should equal the total of Broker + Credit + Application.

This is what I have so far on the Command Button located on my form.

Dim intBroker As Integer
Dim intCredit As Integer
Dim intApplication As Integer
Dim intRefund As Integer

intRefund = intBroker + intCredit + intApplication

I don't know what to do after this. Can you please help?

 

[Rick B]

Why do you not just add a new unbound field and put your formula in it...

=[Broker] + [Credit] + [Application]

Note that you should not store "refund" in your table. You only store the
figures that make it up, then you calculate it when you need it in your
forms, queries, or reports. Storing a calculated value is almost always a
bad idea if you have a normalized database. as one of our MVP's wrote...

Storing derived data such as this in your table accomplishes three things:
it wastes disk space; it wastes time (almost any calculation will be MUCH
faster than a disk fetch); and most importantly, it risks data corruption.
If one of the underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect that fact.



Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it -in the control source of a Form
or a Report textbox.



John W. Vinson[MVP]

 

[Jose Aleman]

That's what I have. But, there is another field Name Status where if the
refund > 400 then Status = Research

Else Status = Closed.

Status is currently a drop down list and user wanted to automatically
populate. I figured if I learn how to write the calculation, when I get my
results, I can write the If statement on the Command as well.

Why do you not just add a new unbound field and put your formula in it...

=[Broker] + [Credit] + [Application]

Note that you should not store "refund" in your table. You only store the
figures that make it up, then you calculate it when you need it in your
forms, queries, or reports. Storing a calculated value is almost always a
bad idea if you have a normalized database. as one of our MVP's wrote...

Storing derived data such as this in your table accomplishes three things:
it wastes disk space; it wastes time (almost any calculation will be MUCH
faster than a disk fetch); and most importantly, it risks data corruption.
If one of the underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect that fact.



Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it -in the control source of a Form
or a Report textbox.



John W. Vinson[MVP]




--
Rick B

Hi,

I wanted to create a calculate button.

My fields

Field Name Data Type Length
Broker Number Double
Credit Number Double
Application Number Double
Refund Number Double

Refund should equal the total of Broker + Credit + Application.

This is what I have so far on the Command Button located on my form.

Dim intBroker As Integer
Dim intCredit As Integer
Dim intApplication As Integer
Dim intRefund As Integer

intRefund = intBroker + intCredit + intApplication

I don't know what to do after this. Can you please help?

 

[Rick B]

Again, you could use an unbound text box with an IIF statement in it.

And, again, you would not want to store the value.

--
Rick B

That's what I have. But, there is another field Name Status where if the
refund > 400 then Status = Research

Else Status = Closed.

Status is currently a drop down list and user wanted to automatically
populate. I figured if I learn how to write the calculation, when I get
my
results, I can write the If statement on the Command as well.

Why do you not just add a new unbound field and put your formula in it...

=[Broker] + [Credit] + [Application]

Note that you should not store "refund" in your table. You only store
the
figures that make it up, then you calculate it when you need it in your
forms, queries, or reports. Storing a calculated value is almost always
a
bad idea if you have a normalized database. as one of our MVP's wrote...

Storing derived data such as this in your table accomplishes three
things:
it wastes disk space; it wastes time (almost any calculation will be MUCH
faster than a disk fetch); and most importantly, it risks data
corruption.
If one of the underlying fields is subsequently edited, you will have
data
in your table WHICH IS WRONG, and no automatic way to detect that fact.



Just redo the calculation whenever you need it, either as a calculated
field
in a Query or just as you're now doing it -in the control source of a
Form
or a Report textbox.



John W. Vinson[MVP]




--
Rick B

Hi,

I wanted to create a calculate button.

My fields

Field Name Data Type Length
Broker Number Double
Credit Number Double
Application Number Double
Refund Number Double

Refund should equal the total of Broker + Credit + Application.

This is what I have so far on the Command Button located on my form.

Dim intBroker As Integer
Dim intCredit As Integer
Dim intApplication As Integer
Dim intRefund As Integer

intRefund = intBroker + intCredit + intApplication

I don't know what to do after this. Can you please help?