VBA code to perform calculations

[Alberta Rose]

Hello.

Some background for my question. I have a db that contains (among other
things) cost codes and cost types. I have written IIf statements for this,
but now think that it's going to get way too big, I want to use VBA coding.

For example. If cost code/cost type = 20110.5320 (a cost field) and is
greater than zero, then take this amount and divide it by txtFirewatchLabEst
(which is an manhour field). Result returned in currency format.

I have over 40 of these scenareos to perform these calculations.

In the next field:

If cost code/cost type = 20110.5320 (a cost field) and is creater than zero,
then take this amount and divide it by 13210.5320 (a cost field as well) and
return the result in % format.

I have over 30 different combinations of these as well.

Any suggestions on how to get going with the VBA coding?

Laurie

 

[Jeff Boyce]

Laurie

"How" depends on "what" ... and I don't have a very clear picture of what
data and data structure you're working with.

Are you saying that you have a table with one field for each code code?
(i.e., a literal interpretation of "in the next field"). That would be a
very spreadsheetly way of setting up the data, but would only cause you and
Access headaches!

Have you looked into using one table with cost codes and "multipliers", and
another with the raw starting points you seem to be using? If this were an
appropriate model (I can't tell from your description), you might be able to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Alberta Rose]

in the contract table, cost code is a field and cost type is a field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210" And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

....but I have many more to add, and there must be an easier way to do this.

 

[Jeff Boyce]

If the combination of values for your [costtype] and [costcode] fields is
set and known, what about the idea of creating a simple lookup table to hold
those valid combinations. That way, you could join your main table (still
not clear what that is) with the type/code table and get the correct
calculation.

This approach would also mean that if you ever alter or add a valid
combination, you just change it in the lookup table.

Or am I still not seeing your overall table structure...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

in the contract table, cost code is a field and cost type is a field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

...but I have many more to add, and there must be an easier way to do
this.

Laurie

"How" depends on "what" ... and I don't have a very clear picture of what
data and data structure you're working with.

Are you saying that you have a table with one field for each code code?
(i.e., a literal interpretation of "in the next field"). That would be a
very spreadsheetly way of setting up the data, but would only cause you
and
Access headaches!

Have you looked into using one table with cost codes and "multipliers",
and
another with the raw starting points you seem to be using? If this were
an
appropriate model (I can't tell from your description), you might be able
to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




.

 

[Alberta Rose]

Hi Jeff. Yes I already have a table that holds the combinations of cost
codes and cost types. It is called tblProjectChartofAccounts. The main
table is called tblContractHoursCosts and includes fields such as contract
number, customer, owner, cost code, cost type, description, quantity,
diameter, height, price, etc.

Once I write this query with the expressions, would I then code in VBA to
run this query?

Sorry for the silly questions, some days....

If the combination of values for your [costtype] and [costcode] fields is
set and known, what about the idea of creating a simple lookup table to hold
those valid combinations. That way, you could join your main table (still
not clear what that is) with the type/code table and get the correct
calculation.

This approach would also mean that if you ever alter or add a valid
combination, you just change it in the lookup table.

Or am I still not seeing your overall table structure...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

in the contract table, cost code is a field and cost type is a field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

...but I have many more to add, and there must be an easier way to do
this.

Laurie

"How" depends on "what" ... and I don't have a very clear picture of what
data and data structure you're working with.

Are you saying that you have a table with one field for each code code?
(i.e., a literal interpretation of "in the next field"). That would be a
very spreadsheetly way of setting up the data, but would only cause you
and
Access headaches!

Have you looked into using one table with cost codes and "multipliers",
and
another with the raw starting points you seem to be using? If this were
an
appropriate model (I can't tell from your description), you might be able
to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hello.

Some background for my question. I have a db that contains (among
other
things) cost codes and cost types. I have written IIf statements for
this,
but now think that it's going to get way too big, I want to use VBA
coding.

For example. If cost code/cost type = 20110.5320 (a cost field) and is
greater than zero, then take this amount and divide it by
txtFirewatchLabEst
(which is an manhour field). Result returned in currency format.

I have over 40 of these scenareos to perform these calculations.

In the next field:

If cost code/cost type = 20110.5320 (a cost field) and is creater than
zero,
then take this amount and divide it by 13210.5320 (a cost field as
well)
and
return the result in % format.

I have over 30 different combinations of these as well.

Any suggestions on how to get going with the VBA coding?

Laurie


.

.

 

[Jeff Boyce]

What are the circumstances under which you want this to happen?

Are YOU always going to be the one to run this? Will there be a "user" who
needs to run it?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff. Yes I already have a table that holds the combinations of cost
codes and cost types. It is called tblProjectChartofAccounts. The main
table is called tblContractHoursCosts and includes fields such as contract
number, customer, owner, cost code, cost type, description, quantity,
diameter, height, price, etc.

Once I write this query with the expressions, would I then code in VBA to
run this query?

Sorry for the silly questions, some days....

If the combination of values for your [costtype] and [costcode] fields is
set and known, what about the idea of creating a simple lookup table to
hold
those valid combinations. That way, you could join your main table
(still
not clear what that is) with the type/code table and get the correct
calculation.

This approach would also mean that if you ever alter or add a valid
combination, you just change it in the lookup table.

Or am I still not seeing your overall table structure...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

in the contract table, cost code is a field and cost type is a field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

...but I have many more to add, and there must be an easier way to do
this.


:

Laurie

"How" depends on "what" ... and I don't have a very clear picture of
what
data and data structure you're working with.

Are you saying that you have a table with one field for each code
code?
(i.e., a literal interpretation of "in the next field"). That would
be a
very spreadsheetly way of setting up the data, but would only cause
you
and
Access headaches!

Have you looked into using one table with cost codes and
"multipliers",
and
another with the raw starting points you seem to be using? If this
were
an
appropriate model (I can't tell from your description), you might be
able
to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

message
Hello.

Some background for my question. I have a db that contains (among
other
things) cost codes and cost types. I have written IIf statements
for
this,
but now think that it's going to get way too big, I want to use VBA
coding.

For example. If cost code/cost type = 20110.5320 (a cost field) and
is
greater than zero, then take this amount and divide it by
txtFirewatchLabEst
(which is an manhour field). Result returned in currency format.

I have over 40 of these scenareos to perform these calculations.

In the next field:

If cost code/cost type = 20110.5320 (a cost field) and is creater
than
zero,
then take this amount and divide it by 13210.5320 (a cost field as
well)
and
return the result in % format.

I have over 30 different combinations of these as well.

Any suggestions on how to get going with the VBA coding?

Laurie


.

.

 

[Alberta Rose]

This is for a view closeout report. It will be used by everyone to pull up
the costs and hours per contract number and I need it to have the
calculations as described above to be populated in the report. Once this is
coded, the same calculations will be made on any contract number chosen.

What are the circumstances under which you want this to happen?

Are YOU always going to be the one to run this? Will there be a "user" who
needs to run it?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff. Yes I already have a table that holds the combinations of cost
codes and cost types. It is called tblProjectChartofAccounts. The main
table is called tblContractHoursCosts and includes fields such as contract
number, customer, owner, cost code, cost type, description, quantity,
diameter, height, price, etc.

Once I write this query with the expressions, would I then code in VBA to
run this query?

Sorry for the silly questions, some days....

If the combination of values for your [costtype] and [costcode] fields is
set and known, what about the idea of creating a simple lookup table to
hold
those valid combinations. That way, you could join your main table
(still
not clear what that is) with the type/code table and get the correct
calculation.

This approach would also mean that if you ever alter or add a valid
combination, you just change it in the lookup table.

Or am I still not seeing your overall table structure...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

in the contract table, cost code is a field and cost type is a field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

...but I have many more to add, and there must be an easier way to do
this.


:

Laurie

"How" depends on "what" ... and I don't have a very clear picture of
what
data and data structure you're working with.

Are you saying that you have a table with one field for each code
code?
(i.e., a literal interpretation of "in the next field"). That would
be a
very spreadsheetly way of setting up the data, but would only cause
you
and
Access headaches!

Have you looked into using one table with cost codes and
"multipliers",
and
another with the raw starting points you seem to be using? If this
were
an
appropriate model (I can't tell from your description), you might be
able
to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

message
Hello.

Some background for my question. I have a db that contains (among
other
things) cost codes and cost types. I have written IIf statements
for
this,
but now think that it's going to get way too big, I want to use VBA
coding.

For example. If cost code/cost type = 20110.5320 (a cost field) and
is
greater than zero, then take this amount and divide it by
txtFirewatchLabEst
(which is an manhour field). Result returned in currency format.

I have over 40 of these scenareos to perform these calculations.

In the next field:

If cost code/cost type = 20110.5320 (a cost field) and is creater
than
zero,
then take this amount and divide it by 13210.5320 (a cost field as
well)
and
return the result in % format.

I have over 30 different combinations of these as well.

Any suggestions on how to get going with the VBA coding?

Laurie


.



.

.

 

[Jeff Boyce]

I guess I'm still not seeing the bigger picture.

If you need to include some calculated values in a report, and if you are
using a query to "feed" that report its data, it seems to me you could use
that query to do the calculation you're after.

If you need to join your 'raw data' table to the table holding the valid
combinations, OK. In the fuzzy view I have, that would be exactly how I'd
accomplish the calculations.

(but I'm pretty sure I'm still missing something... hopefully other
newsgroup readers can help you with this)

Best of Luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

This is for a view closeout report. It will be used by everyone to pull
up
the costs and hours per contract number and I need it to have the
calculations as described above to be populated in the report. Once this
is
coded, the same calculations will be made on any contract number chosen.

What are the circumstances under which you want this to happen?

Are YOU always going to be the one to run this? Will there be a "user"
who
needs to run it?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff. Yes I already have a table that holds the combinations of
cost
codes and cost types. It is called tblProjectChartofAccounts. The
main
table is called tblContractHoursCosts and includes fields such as
contract
number, customer, owner, cost code, cost type, description, quantity,
diameter, height, price, etc.

Once I write this query with the expressions, would I then code in VBA
to
run this query?

Sorry for the silly questions, some days....

:

If the combination of values for your [costtype] and [costcode] fields
is
set and known, what about the idea of creating a simple lookup table
to
hold
those valid combinations. That way, you could join your main table
(still
not clear what that is) with the type/code table and get the correct
calculation.

This approach would also mean that if you ever alter or add a valid
combination, you just change it in the lookup table.

Or am I still not seeing your overall table structure...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

message
in the contract table, cost code is a field and cost type is a
field.
Typically a contract has between 30 and 50 of these combinations.

In the past I've used the following in the txt box on the report:

=IIf(IsNull([costcode]) Or
IsNull([costtype]),0,IIf([costcode]="013210"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110"
And
[costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201"
And
[costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301"
And
[costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([costcode]="061101"
And
[costtype]="05310",[txtEstimatedCost]/[txtSiteTeamLabEst],IIf([costcode]="031101"
And
[costtype]="05110",[txtEstimatedCost]/[txtOfficeTeamLabEst],IIf([costcode]="042211"
And
[costtype]="05110",[txtEstimatedCost]/[txtEngLabEst],IIf([costcode]="045311"
And
[costtype]="05130",[txtEstimatedCost]/[txtThirdPartyLabEst],IIf([costcode]="032101"
And
[costtype]="05110",[txtEstimatedCost]/[txtProcurementLabEst],Null))))))))))

...but I have many more to add, and there must be an easier way to
do
this.


:

Laurie

"How" depends on "what" ... and I don't have a very clear picture
of
what
data and data structure you're working with.

Are you saying that you have a table with one field for each code
code?
(i.e., a literal interpretation of "in the next field"). That
would
be a
very spreadsheetly way of setting up the data, but would only cause
you
and
Access headaches!

Have you looked into using one table with cost codes and
"multipliers",
and
another with the raw starting points you seem to be using? If this
were
an
appropriate model (I can't tell from your description), you might
be
able
to
use a query instead...

More info, please

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is",
with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

message
Hello.

Some background for my question. I have a db that contains
(among
other
things) cost codes and cost types. I have written IIf statements
for
this,
but now think that it's going to get way too big, I want to use
VBA
coding.

For example. If cost code/cost type = 20110.5320 (a cost field)
and
is
greater than zero, then take this amount and divide it by
txtFirewatchLabEst
(which is an manhour field). Result returned in currency format.

I have over 40 of these scenareos to perform these calculations.

In the next field:

If cost code/cost type = 20110.5320 (a cost field) and is creater
than
zero,
then take this amount and divide it by 13210.5320 (a cost field
as
well)
and
return the result in % format.

I have over 30 different combinations of these as well.

Any suggestions on how to get going with the VBA coding?

Laurie


.



.

.