Make this Row Percentages

[perrymans]

I am storing numbers in a Transaction table.

The numbers are used in a Crosstab query, which then fills the textboxes on a report.

Simple language of what I want to do is:

If the TaskName Like "*%*" Then add a "%" symbol to the end of TransactionValue.

Obviously I can not do it in the query because it is a crosstab query and it can't 'Sum' a text value.

So how would I di this?

Thanks. Sean.

 

[dhookom]

You could add another text box to the right of your TransactionValue with a control source like:

=IIf(Instr([YourField],"%")>0,"%",Null)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

There isn't a '%' symbol already in the field, it is just a number.

I tried:

=IIf([TaskName] Like "*%*", [TransactionValue]&"%", [TransactionValue]

But that did not work.

The name of the Tasks all have a '%' in them (i.e. '% of ACO Errors')

Perhaps it is the use of 'Like'?

Thanks. Sean.

 

[dhookom]

The % symbol is used as a wild card. Isn't the % symbol in your TaskName field? Try:
=IIf(Instr([TaskName],"%")>0,"%",Null)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

Still doesn't work, the textbox is always blank!

I am using it in the detail portion of the report, it just isn't returning anything.

Oddly enough, if I apply conditional formatting to the box, it worked (I replaced the Null with "N" to see if it was firing at all).

Too weird.

Any thoughts? Sean.

 

[dhookom]

I have tried this twice with an expression like:
=IIf(Instr([TaskName],"%")>0,"%",Null)
I also set my TaskName value in a record to '% of ACO Errors' which worked.

What exact expression did you use and did you check all the properties of your text box?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]