Report parameters

[Alberta Rose]

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Klatuu]

You can test for the codes to be excluded in the Sum function using the IIf
function:

=Sum(IIf([cost code/cost types] = 091110.05970 Or IIf([cost code/cost types]
= 092110.05970,0,[Estimated Cost]))
cost

code/cost types 091110.05970 or 092110.05970

--
Dave Hargis, Microsoft Access MVP

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Alberta Rose]

would i enter this in the text box under the Control Source?

You can test for the codes to be excluded in the Sum function using the IIf
function:

=Sum(IIf([cost code/cost types] = 091110.05970 Or IIf([cost code/cost types]
= 092110.05970,0,[Estimated Cost]))
cost

code/cost types 091110.05970 or 092110.05970

--
Dave Hargis, Microsoft Access MVP

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[KARL DEWEY]

I think it should be more like this as I assume that [cost code/cost types]
is a text field --
=Sum(IIf([cost code/cost types] = "091110.05970" Or [cost code/cost types]
= "092110.05970",0,[Estimated Cost]))

--
Build a little, test a little.

You can test for the codes to be excluded in the Sum function using the IIf
function:

=Sum(IIf([cost code/cost types] = 091110.05970 Or IIf([cost code/cost types]
= 092110.05970,0,[Estimated Cost]))
cost

code/cost types 091110.05970 or 092110.05970

--
Dave Hargis, Microsoft Access MVP

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Duane Hookom]

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

 

[Alberta Rose]

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Duane Hookom]

Your table with the unique cost codes could have a field that stores whether
or not the code should be considered. You could then add this table to your
report's record source and use the field.

--
Duane Hookom
Microsoft Access MVP

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Alberta Rose]

I've made the table with the unique records in them. I have added this table
to the qryLink which feeds the report. I have the text boxes set up in the
report footer, and now am trying to figure out how to link them...

Your table with the unique cost codes could have a field that stores whether
or not the code should be considered. You could then add this table to your
report's record source and use the field.

--
Duane Hookom
Microsoft Access MVP

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP


:

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Duane Hookom]

What are the fields in the new table? What is the record source of your
report? I expect something like:
SELECT ....
FROM .... JOIN .... ON ....


--
Duane Hookom
Microsoft Access MVP

I've made the table with the unique records in them. I have added this table
to the qryLink which feeds the report. I have the text boxes set up in the
report footer, and now am trying to figure out how to link them...

Your table with the unique cost codes could have a field that stores whether
or not the code should be considered. You could then add this table to your
report's record source and use the field.

--
Duane Hookom
Microsoft Access MVP

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

:

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP


:

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Alberta Rose]

Sorry for dragging this out like this. The record source of my report is
thru qryLink, which pulls info from tblContractHoursCosts.

From the tblContractHoursCosts, I have copied all the data that I want in
the footer section to tblCloseoutSummaries and the fields in this new table
are CostCodeSummary, CostTypeSummary, EstimatedCostSummary and
ActualCostSummary.

From tblCloseoutSummaries, I have created two queries:
qryLinkAdminforCloseoutReport and qryLinkTSFforCloseoutReport.

In the text boxes I added to the footer section, I have named one TSF and
one Admin. I think I need to know how to tie the queries to each box. Am I

What are the fields in the new table? What is the record source of your
report? I expect something like:
SELECT ....
FROM .... JOIN .... ON ....


--
Duane Hookom
Microsoft Access MVP

I've made the table with the unique records in them. I have added this table
to the qryLink which feeds the report. I have the text boxes set up in the
report footer, and now am trying to figure out how to link them...

Your table with the unique cost codes could have a field that stores whether
or not the code should be considered. You could then add this table to your
report's record source and use the field.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

:

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP


:

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???

 

[Duane Hookom]

I don't know what track you are on since I can't see the actual field names
in your report or what types of values they contain. I also don't know if you
actually created a field in your cost type table that identifies whether or
not to include it in some of your total calculations.

If you aren't using subreports then I expect you should have a single Record
Source that I am having trouble understanding. Can you provide some sample
records?

--
Duane Hookom
Microsoft Access MVP

Sorry for dragging this out like this. The record source of my report is
thru qryLink, which pulls info from tblContractHoursCosts.

From the tblContractHoursCosts, I have copied all the data that I want in
the footer section to tblCloseoutSummaries and the fields in this new table
are CostCodeSummary, CostTypeSummary, EstimatedCostSummary and
ActualCostSummary.

From tblCloseoutSummaries, I have created two queries:
qryLinkAdminforCloseoutReport and qryLinkTSFforCloseoutReport.

In the text boxes I added to the footer section, I have named one TSF and
one Admin. I think I need to know how to tie the queries to each box. Am I

What are the fields in the new table? What is the record source of your
report? I expect something like:
SELECT ....
FROM .... JOIN .... ON ....


--
Duane Hookom
Microsoft Access MVP

I've made the table with the unique records in them. I have added this table
to the qryLink which feeds the report. I have the text boxes set up in the
report footer, and now am trying to figure out how to link them...

:

Your table with the unique cost codes could have a field that stores whether
or not the code should be considered. You could then add this table to your
report's record source and use the field.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for that, yes our costcode/types do tend to change almost 2x per
year lately, so hardcoding may be more of a pain. If I put the cost codes/
cost types in a unique table, would I then add them to my report in a
subreport?

Laurie

:

I would not hardcode the two values since some day there may be one or three
or four. Whether or not a cost type should "not included in the total" should
be stored in a table some where (preferrably your table of unique cost types).

I wouldn't assume your codes won't change and I wouldn't want to maintain
expressions in queries or reports. I would maintain data in tables.

--
Duane Hookom
Microsoft Access MVP


:

Hi. I need help writing an expression. On my report I have multiple cost
codes/cost types listed as well as their estimated and actual costs. I have
created 2 boxes where all the estimate costs and actual costs are totalled.
The problem is that there are two cost code/cost types that need to appear on
the report but not included in the total.

So far in this totalling box I have put =Sum([Estimated Cost]), but I need
to know what to add to this to NOT select the $$ values associated with cost
code/cost types 091110.05970 or 092110.05970. I have tried building a query
that would not include these cc/ct's but I don't know how to relate them in
this report???