Want to Use a WHERE clause for TextBox in Report

[Spidey3721]

I have a report based on a table that includes project costs

tbl [ProjCosts]
[CostType] - ("Actual", or "Budget")
[CostAmount]

I am trying to have my report list Budget costs side by side with Actaul
costs. I would like to use a textbox with a simple SQL statement with a
WHERE clause, but it appears that I cannot use a SQL statement in the
control source. Is there a workaround? The help file says that I CAN bind a
textbox control source to a SQL statement.

SELECT ProjCosts.CostAmount
FROM ProjCosts
WHERE (((ProjCosts.CostEntryType)="actual"));

 

[JohnFol]

I've never seen that written in the help text.

If the field is present in the reports underlying recordset, why not just
set the source of the text box to be the filed??

 

[Spidey3721]

It's not.

I have a [CostAmount] field in the underlying recordset, but want to have
two different TextBox fields in the report group, one that shows
[CostAmount] WHERE [CostType]="Actual" and one that shows [CostAmount] WHERE
[CostType]="Budget"

I've never seen that written in the help text.

If the field is present in the reports underlying recordset, why not just
set the source of the text box to be the filed??

I have a report based on a table that includes project costs

tbl [ProjCosts]
[CostType] - ("Actual", or "Budget")
[CostAmount]

I am trying to have my report list Budget costs side by side with Actaul
costs. I would like to use a textbox with a simple SQL statement with a
WHERE clause, but it appears that I cannot use a SQL statement in the
control source. Is there a workaround? The help file says that I CAN

bind

a
textbox control source to a SQL statement.

SELECT ProjCosts.CostAmount
FROM ProjCosts
WHERE (((ProjCosts.CostEntryType)="actual"));

 

[Albert D. Kallal]

You can't put sql directly into a text box. Further, if you *could* put that
sql into a text box, the resulting sql the way you have written it would
return a very large number of records, and how would ms-access know which
record out of that large set to put into that text box?

I am certain if you actually fire up the query builder and place:

SELECT ProjCosts.CostAmount
FROM ProjCosts
WHERE (((ProjCosts.CostEntryType)="actual"));

in the sql for the query builder, you will get a "lot" of records return,
and how would ms-access know what value out of that large number of records
to put into the text box?


You have:
tbl [ProjCosts]
[CostType]= "Actual" or "Budget"
[CostAmount]

What I would do is build a query that has all the fields you need, and then
add two additional fields. These fields will return ONLY values for the
given costtype, and "zero for others..

So, in the query builder, type in:


CostActualiif([costType] = 'Actual',CostAmount,0))

And make another column:

CostBudgetiif([costType] = 'Budget',CostAmount,0))

Now, simply base your existing report on this new query, and you will see in
the drop down list of fields, two additional columns that you can use.

You also can just place some text boxes in the reprot, and use a iff
statmane also.

So, the contorl souce of the text box could be:

=(iif([costType] = 'Budget',[CostAmount],0))

I tend to like using the query, as then all reports, forms etc that use that
query have ready made fields to use.

 

[Spidey3721]

That all makes alot of sense, except I still think I have a problem - it may
be that I have a fundamental problem with the way that my table is designed

With the table that I have
tbl [ProjCosts]
[ProjectID] - Foreign key number that identifies the Project this cost is
associated with (example: 00-23)
[CostCodeID] - Foreign key that identifies what the cost is (example: 15-500
(plumbing))
[CostType] - The type of cost that is being entered ("Actual", "Budget")
[Cost] - Thew actual dollar amount for the cost record

What I am looking for is a report that shows has the following columns
headings - (I am already grouping this report by [ProjectID])

Column Headings: Cost Code; Budget Cost (the budget value, if any, that has
been entered for that job and that cost code); Actual cost (the actual cost,
if any that is associated...)

Examples of record entries are as follows:
ProjectID=00-23, CostCodeID=0001, CostType=Budget, Cost=$23,000
ProjectID=00-23, CostCodeID=0001, CostType=Actual, Cost=$21,346
ProjectID=00-23, CostCodeID=0002, CostType=Budget, Cost=$156,000
ProjectID=00-23, CostCodeID=0001, CostType=Actual, Cost=$160,357

So I want my Report, which is grouped by ProjectID to show the following:

REPORT
(under the 00-23 group...)
[CostCodeIDTxt]:0001; [BudgetTxt]:$23,000; [ActualTxt]:$21,346
[CostCodeIDTxt]:0002; [BudgetTxt]:$156,000; [ActualTxt]:$160,357

I set it up this way (with the [CostType] field, so that I could add
different cost types down the road (in addition to "Budget" and "Actual" ;
say maybe "at 50% complete" - for use in other reports).

I believe that both of your solutions would result in something like as
follows:

REPORT
(under the 00-23 group...)
[CostCodeIDTxt]:0001; [BudgetTxt]:$0; [ActualTxt]:$21,346
[CostCodeIDTxt]:0001; [BudgetTxt]:$23,000; [ActualTxt]:$0
[CostCodeIDTxt]:0002; [BudgetTxt]:$0; [ActualTxt]:$160,357
[CostCodeIDTxt]:0002; [BudgetTxt]:$156,000; [ActualTxt]:$0

You can't put sql directly into a text box. Further, if you *could* put that
sql into a text box, the resulting sql the way you have written it would
return a very large number of records, and how would ms-access know which
record out of that large set to put into that text box?

I am certain if you actually fire up the query builder and place:

SELECT ProjCosts.CostAmount
FROM ProjCosts
WHERE (((ProjCosts.CostEntryType)="actual"));

in the sql for the query builder, you will get a "lot" of records return,
and how would ms-access know what value out of that large number of records
to put into the text box?


You have:
tbl [ProjCosts]
[CostType]= "Actual" or "Budget"
[CostAmount]

What I would do is build a query that has all the fields you need, and then
add two additional fields. These fields will return ONLY values for the
given costtype, and "zero for others..

So, in the query builder, type in:


CostActualiif([costType] = 'Actual',CostAmount,0))

And make another column:

CostBudgetiif([costType] = 'Budget',CostAmount,0))

Now, simply base your existing report on this new query, and you will see in
the drop down list of fields, two additional columns that you can use.

You also can just place some text boxes in the reprot, and use a iff
statmane also.

So, the contorl souce of the text box could be:

=(iif([costType] = 'Budget',[CostAmount],0))

I tend to like using the query, as then all reports, forms etc that use that
query have ready made fields to use.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[Albert D. Kallal]

I see no reason why my approach would not work, especially if those two
additional fields are added to the query that the report is based on.

You will simply then just add those two additional columns to the query. If
you then include them in the grouping section of that report, and place a
sum([fieldName]) around each of those two fields, then I see no reason why
you don't get the results you want?

(I assume that you are using the group footer now to show the
totals..right?)

Once the iff are setup in the query, then for all purposes, you have two
additional columns, and one column will always will return 0 for a given
record, and the other column will return the cost value.

Simply sum those values in the footing of the break section, and away you
go.

In fact, try placing the those two extra fields into the detail section of
the report for a test to see what is going on. When done, you can either
remove the fields from the detail section or even just turn off the visible
property of the detail section when done (I assume you don't have much, if
anything at all in the detail section, since your example only has the
totals. Turning off the visible of the detail section, you can have more fun
testing this.

Anyway, add try adding some text boxes to the group footer, and use
=sum([CostActual]) for the control source. and try =sum([costBudjet])