Solved Format textbox with special case for "1" (1 Viewer)

[AOB]

I have a textbox control that displays the number of workdays between two dates. I am using a custom format to suffix " days" after the number for clarity :

#,##0" days";[Red]#,##0" days"

I'd like to modify this so that when the value is 1, it displays "1 day" rather than "1 days"

Tried using this :

[=1]0" day";#,##0" days";[Red]#,##0" days"

But it's not liking it (appears like this)

[=1]7 day

Any idea how to appropriately customise a number format with conditions? (Fully admit I'm nicking the custom format from Excel rather than Access so suspect the issue is that Access only likes [Positive];[Negative];[Zero];[Text] but hoping there is some way to specify conditions in a similar manner?

Thanks!

AOB

 

[June7]

Could have "day/days" text in a separate textbox and use an IIf() expression to display appropriately.

 

[Gasman]

Put the last s in ()

 

[CJ_London]

If this is on a single form you could use code to change the format depending on the value

Code might be in the current event and/or the after update event of whatever controls contribute to calculating the value

 

[arnelgp]

you can try Adding another Unbound textbox and hide the Original textbox with Calculation of days.
the ControlSource of the New Unbound textbox:

=[theOrigTextboxWithCalculation] & " day" & IIF([theOrigTextboxWithCalculation] > 1, "s","")

now use Conditional format to Color this textbox:

Expression: [theOrigTextboxWithCalculation] > 1
Color: Red

 

[JordanHarper]

Can I ask a question about it?

 

[CJ_London]

Ask away but usually better to start your own thread

 

[AOB]

Apologies for the delay in acknowledging these replies, there seems to be a weird firewall / extension block on the site from my work computer that wasn't there previously; I can log in and view threads but the requisite textbox controls for replying (or creating new threads) are obscured. Have to log in from home in order to contribute to any threads, even my own. Will need to look into that but that's for another day...

Anyway - thanks everybody! Have opted to go with the suggestions (or a flavour thereof...) of @June7 and @arnelgp and hold the raw numerical value in a hidden textbox with a "display" version combining the value and the "units", handling singular vs multiple. Works perfectly. Thanks!

Incidentally, the only reason I thought a customised number format of this nature was even plausible was from this webpage :

Custom Excel Number Format : Create conditional number formats

Which implied that this was natively possible within Excel (to be honest, as a reasonably experienced Excel user, I was surprised I'd never heard of this before, which should probably have been a red flag...) I figured customisable number formats would therefore probably be standard across all Office applications. So, I jumped to not one but in fact several conclusions...

Having had some time to tinker around with the instructions in Excel directly, it appears that this is either a) complete baloney or b) requires a third-party add-in or extension onto Excel in order for it to be available. If the latter, then it wasn't very clear - to me anyway? - that this was the case. Even re-reading it now, I feel it still implies that this is a native feature but maybe I'm interpreting it incorrectly (?)

Anyway - just wanted to clarify where I was coming from in case people were scratching their heads wondering where on earth I was getting that from! (Incidentally, if anyone is reading this and reckons this kind of thing is natively available in Excel, i would love to understand how!)

 

[AOB]

Sorry, final comment on this...

Turns out Excel does provision the option for custom conditional number formatting natively. You can prefix each format with a condition enclosed in square brackets. In doing so, though, the standard layout of custom number formats (i.e. Positive;Negative;Zero;Text) goes out the window and is replaced with the conditional layout instead (i.e. [Condition1]Format1;[Condition2]Format2).

However...

When using conditions in a custom number format in Excel, you are limited to using only two. Anything more than that and it breaks. So you can only specify a pair of conditions separated by a single semi-colon.

So (in Excel) you can use the following as a perfectly valid custom number format for suffixing units after numerals and handling single vs multiples within those units :

[=1]0" day";#,##0" days"

But what you can't do - which is of course what I did - is have more than two such conditions (e.g. to cover negatives, or zeroes, or any other custom condition for that matter) :

[=1]0" day";#,##0" days";[Red]#,##0" days"

As soon as you do that, it reverts back to the standard custom Positive;Negative;Zero;Text framework and simply adopts the leading condition in square brackets (the [=1]) as part of the format for each of the potential positive, negative, zero & string inputs respectively.

And in any case - my other supposition (that number format strings such as these might be universal across all Microsoft Office applications) seems to be entirely false. I tried using the exact same custom number format in Access form design and, while it will let me add it, it doesn't apply it at runtime.

But the good news is, I do at least now have some tuple-sensitive number formatting in my Excel outputs from the Access database

(Be aware, of course, that because the two conditions are basically "is 1" or "is not 1", any negative numbers are going to appear exactly the same as positive numbers - the values may be retained but they will look exactly the same on a worksheet - so I can only use it on a column that I know can only contain positive values...)

 

[DickyP]

Surprisingly I found the following to be more efficient that Iif() etc.

Variable= (Value > 1) + 2 ' So variable will be 1 or 2

"Day" & Choose(Variable, "", "s")

So if the variable is 1 it takes the first value of nullstring in the Choose list and 2 adds an 's'.

Can't hazard a guess why it seems to work quicker.

 

[cheekybuddha]

Can't hazard a guess why it seems to work quicker.

Interesting.

How many iterations did you require to be able to see a noticeable time difference?

Also, did you perform this in VBA? I ask because, IIRC, IIf() works differently in a query than in VBA.

In VBA I think both the True and False parts are evaluated, even if the condition is True; but in a query the function 'short-circuits' and doesn't bother evaluating the False part if the condition is True (I *think* I've got that the right way round )

So maybe if used as an expression in a query then the IIf() might not be so much slower.