Textbox with "!" if value is negative, or actual number if positive (AC2007)

[AOB]

Textbox with "!" if value is negative, or actual number if positive (AC2007)

Hi everybody,

Does anybody know if it's possible to format a textbox such that a negative value doesn't show numerically but in some other form (like a cross or exclamation mark from the Wingdings font, for example?)

I have a couple of textboxes which take their values from a function. The function is designed to return a long integer value which should always be greater than or equal to zero (the function counts the number of outstanding e-mails in a given Outlook folder, hence it should always be zero or higher)

However, as part of my error handling, if the function can't connect to the Outlook folder for whatever reason (for example, if the user does not have the appropriate permissions, or they do not have it mapped etc.), then it returns -1 (so I have some sort of value to identify a fail)

So currently, if such an error occurs, my textbox(es) will show -1 (and I can use conditional formatting to highlight this)

But it would be nice if it showed an "X" or "!" instead, and only showed the numerical value for 0 or positive. Is this possible?

Thanks

Al

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

is -1 the only - return possible?

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Well, no, only I have the function defined to return a long integer, hence -1 seemed the most logical option to return for an error. I could change it to a variant and return a string for an error and a long otherwise? But then I'd need to find an appropriate string character in a font set that also includes standard numerical values?

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Sorry, I may have mis-read that - do you mean, is it possible for another negative value to be returned by the function? If so, then no, the function can only return a positive (or zero) value (as it is merely the count of emails in a folder) or -1 if the function fails due to an inability to map to the folder in question (driven by the error handler within the function itself)

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

if so you could try wrapping it in a Replace works.

Example:
=Replace(CStr(Nz(func(1),"")),"-1","X")

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

func being your function

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Also if you edit your function to not define return type you can use this.

=Replace(CStr(Nz(func(1),"-1")),"-1","X")

The function will return null if there is no return type defined.

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

That works! Never thought of wrapping my function in a Replace

I presume this is more efficient than using an IIf?

=IIf(func(1)<0,"X",func(1))

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

yep. One function call rather than two.

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Thought so - I generally try to avoid IIf's wherever possible

If only they would just evaluate the True or False portion rather than both

Or should that be IIf only?...

Thanks Dan!

 

[Galaxiom]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

A much more straightforward solution:
Just enter this as the Format Property of the textbox

;"X"

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Thanks Galaxiom - that was kinda where I was going originally as have used the Format property elsewhere, but only to show True and False as Wingdings characters (tick and cross), not for showing either a number or a character

That said...

Should it not be :

"X";

As I want to show the X for negative numbers?

Also - when I try to put

"X";

into the property box, Access changes it to

\X

I can't get it to retain the double quotes?

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

This seems to work though...

X;#,##0

Access still converts it to this for some reason :

X;"#,##0"

But the results display correctly?

I would have thought the double-quotes should be applied to the text rather than the numericals?...

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

I tell a lie... This doesn't work, still shows -1 rather than X

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Could someone explain to me this formatting stuff lol

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Learning myself - this article may be useful - just reading now, hopefully will figure out the problem

(Always wanted to know the order of the custom format strings, glad I found this!)

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Incidentally, it's : [Positive];[Negative];[Zero];[Null]

So apologies to Galaxiom, it appears your original suggestion should have been correct. Now to figure out why it didn't work...

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

what is your functions described return type if any at all?

 

[BlueIshDan]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

#;'!';#;'!"

or would it be

;'!';;'!"

 

[AOB]

Re: Textbox with "!" if value is negative, or actual number if positive (AC2007)

Function returns a Long Integer

I have it working now, with the following format :

#,##0;!;0

I can't surround the ! with double-quotes in Access, it keeps removing them on me - but it's showing an exclamation mark for a negative value so it's not a problem.

Am curious, though, as to why the double-quotes keep getting edited out (similarly, for the "X", I kept getting \X instead).

If I try "!!" or "XX" instead, they are retained - only for single character strings are the quotes dropping. Puzzling, but not show-stopping...