Add text based on results 2

[TSSTechie]

Howdy,

Is it possible to add text to a report textbox IF there is data present in a specified field.

Ok what I want is probably as clear as mud now

I have a report and I want a text box that shows the field "NoofDaysTaken". I also want the report to add the word " Days" after that field but ONLY if there is data in there. NoofDaysTaken will not always be given and so, the word Days should not show for these records.

Hope you understand what i'm babbling about but I can't think of a better way to describe it

Thanks in advance

TSSTechie

May The Force Be With You

 

[glalsop]

Use something like this as the control source for a text box:

=iif(isnull([NoofDaysTaken]),"",[NoofDaysTaken] & " Days")

-Gary

 

[dhookom]

You can also try:
=[NoOfDaysTaken] + " Days"
The "+" will carry null field values to the entire expression.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TSSTechie]

Howdy,

Thanks a million

Just the job

TSSTechie

May The Force Be With You

 

[jckokko]

What if the field [NoOfDaysTaken] does not exist?
In another word, the query didn't return the field.
The query will return the field for next month.
Can I put a place holder?

 

[dhookom]

A query will always return the fields that are in its design. Are you changing the design of the query or is the query a crosstab? What would cause a field/column to not appear?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Yes, the query is a crosstab.

 

[dhookom]

You can specify columns names in the Column Headings property of the crosstab query. Maybe you should tell us what you are attempting to do. Perhaps provide the SQL view of your query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

PARAMETERS [Forms]![?????????]![???] DateTime, [Forms]![?????????]![????] DateTime;
TRANSFORM Sum(tblExpense.Amnt) AS AmntOfSum
SELECT tblDepartment.Name AS DepartmentName, tblExpenseCategory.Category, Sum(tblExpense.Amnt) AS ??
FROM (((tblExpense INNER JOIN tblDepartment ON tblExpense.DepId = tblDepartment.DepId) INNER JOIN tblTransaction ON tblExpense.TransId = tblTransaction.TransId) INNER JOIN tblExpenseCategory ON tblExpense.ExpCategId = tblExpenseCategory.ExpCategId) INNER JOIN tblBudgetThisYearDepartment ON tblDepartment.DepId = tblBudgetThisYearDepartment.BudDepId
WHERE (((tblExpense.ExpDate)>[Forms]![YearExpenseMonthlyReport]![BeginDate] And (tblExpense.ExpDate)<[Forms]![YearExpenseMonthlyReport]![EndDate]))
GROUP BY tblDepartment.Name, tblExpense.ExpCategId, tblExpenseCategory.Category, tblBudgetThisYearDepartment.BudDepAmnt
ORDER BY Format([ExpDate],"mm")
PIVOT Format([ExpDate],"mm");

 

[jckokko]

[Forms]![?????????]![???] = [Forms]!YearExpenseMonthlyReport]![BeginDate]

[Forms]![?????????]![????] = [Forms]!YearExpenseMonthlyReport]![EndDate]

AS ?? = AS Total

 

[dhookom]

I don't understand where this statement came from "What if the field [NoOfDaysTaken] does not exist?"

It would help if you would have clearly stated you had a crosstab query with months formatted as 01, 02, ... where you always wanted every month to display whether there was data for the column or not.

Set your Column Headings property to:
Column Headings:01, 02, 03,...12

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Yes, it could help if I added a statement explaining what I am trying to do. Sorry Duane. I will be more careful next time.

I will try your suggestion and let all know.
Thanks.

 

[jckokko]

Thanks Duane. It worked. It seems that Microsoft Access is full of quick and dirty or inconsistent solutions which are patched as issues occur.

 

[jckokko]

This time I am changing the design of the query.
A customer never used a service so there's no charge.
So the customer's report has no data to display, and I get #Error for the Amnt field. I tried =[Amnt]+ " ", but it didn't work.

 

[dhookom]

Where did you try this expression? I don't see Amnt in the output of your query. If you want your crosstab to return 0 rather than Null, use:
TRANSFORM Val(Nz(Sum(tblExpense.Amnt),0)) AS AmntOfSum


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Here's the query.

PARAMETERS [Forms]![IndividualContributionReport]![cbxIndividualName] Text ( 255 );
SELECT tblContribution.Type, tblContribution.Amnt
FROM tblContribution
WHERE (((tblContribution.Contributor)=[Forms]![IndividualContributionReport]![cbxIndividualName]));

This query may return no record depends on individual contributors. I have a report that generates one page report for a specified contributor. The report is based on this query. I want to display $0 amount instead #Error. Maybe this query needs to be adjusted.

I have another report that basically does the same as above except that it generates reports for all contributors. I rather do not want to generate a report if this query returns nothing for a contributor (instead of putting $0 contribution amount). I have not started this report though although I have a form designed for it.

 

[dhookom]

Ok, so this new question is about a report bound to a select query rather than a crosstab? Are you concerned about showing #error when the report recordsource returns no records?

What is the control source of the text box where you see #Error?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Yes to the first and second questions.

tblContribution.Type, tblContribution.Amnt are the control source where I see #Error.

 

[dhookom]

If a report returns no records then the detail section should not show. Any controls in other sections may show #Error if they are bound to expected records. The solution is to use expressions like:
=IIf([HasData],Sum(Amnt),0)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

I just decided to cancel the Report Open event using "On No Data" event.
Thanks.