Conditional Format Question (1 Viewer)

[CanadianAccessUser]

Hi,

My database tracks statistics for Agents in a call center.
I have reports that show the agent stats.
The report I have a question about shows the difference in stats from last month to this month. I've conditionally formatted the report so that if the number is negative it is red, positive is green. (works as expected)

Question:
How do I say that:
If the value contains a "-" do not include the "-" sign....

The colours stand out way better than the "-" and I don't want to confuse the users with them.

Any advise would be great.

Thanks,
Canadian

 

[BlueIshDan]

You could load the report based on a query that has an extra field in it that you determine the condition on.

Example

SELECT field1, field2, field3, (InStr(1, field3, "-") > 0) As [NewFieldName], Remove(field3, "-") As [NewFieldName2]
FROM [table/query]

And depending on which version of access you have, I can write you a Remove function.

 

[CanadianAccessUser]

Would that solution remove the "-" or make it not visible?
I would prefer to make it not visible.

 

[BlueIshDan]

Well what would happen is you will generate a new field with the removed character and another new field(boolean) stating wether or not there was a character removed.

You only display the field with the removed "-" character while conditioning it against the boolean field you've created.

This can all be done through a query, and depending on your version of access A custom Remove(str, remove) As String function. Thus leaving the structure of your table the same.

 

[BlueIshDan]

Show me your table structure and I will be glad to write you the function and query

 

[pbaldy]

If it's a numeric value you'd probably want Abs() rather than looking for the "-", but I'd just use the Format property of the textbox. You can specify the format for negative numbers and not include the -.

 

[BlueIshDan]

My bad for thinking for both number and string values.

 

[pbaldy]

I'm lazy, so I look for simple solutions.

 

[BlueIshDan]

String Condition​

Number Condition​

 

[CanadianAccessUser]

I'm already working from a query that has queried queries... lol
it is called: qryMetricsDifference-1Month
The fields are:
Coach - Text
Agent - Text
Calls
AHT - formatted as hh:mm:ss (at the report level)
ACW - formatted as hh:mm:ss (at the report level)
Hold - formatted as hh:mm:ss (at the report level)
TransferRate - %
BreakAux - %
PSU
S2S - %
Samples
Quality - %
RecommendRate - %
Surveys
NPS - %
LTR
CSAT
FCR - %
Presentment - %

Is that helpful?

I appreciate your help. This sounds like something way over my head.

 

[BlueIshDan]

see the above post, I figured it out and posted pictures for you.

 

[CanadianAccessUser]

If it's a numeric value you'd probably want Abs() rather than looking for the "-", but I'd just use the Format property of the textbox. You can specify the format for negative numbers and not include the -.

Thanks Paul!

but how do I do that? I don't see anything that refers to negatives...

 

[CanadianAccessUser]

OMG it worked!!!
Thanks BluishDan!!

 

[BlueIshDan]

Haha your welcome,
Thanks pbaldy for the guidance.

 

[pbaldy]

Thanks Paul!

but how do I do that? I don't see anything that refers to negatives...

See attached from help.