how to display only positive numbers in a textbox

[sviggos]

Hi, my name is sviggos and i have the following question:

i have two text boxes in a form that calculate the sum of two different values from a table.

ie. textbox 1 =Sum([Totalinvoice])
textbox 2 =Sum([Totalpayments])

in order to check if the invoices are paid or not and what is the amount of them, i created two more textboxes in order to make some mathematical calculations ie.

textbox 3 =[textbox 1]-[textbox 2]
textbox 4 =[textbox 2]-[textbox 1]

So, both text boxes 3,4 are giving the same number with a different sign depending the sum of of textboxes 1, 2(for example textbox3= 1.000,00 and textbox4= - 1.000,00)

Up to this point everyhting is ok, but

i want to show only the positive numbers in textbox 3 and textbox 4 whenever this occurs. How do i achieve this ?

 

[missinglinq]

If you're saying that you only want the results to be visible if they're positive, you can use Conditional Formatting for this.

Goto Format - Conditional Formatting

Enter

Field Value Is

Less Than

0

Now set the font color to white (or whatever color you have the textbox BackColor set to.)

Hit Okay

 

[MSAccessRookie]

Hi, my name is sviggos and i have the following question:

i have two text boxes in a form that calculate the sum of two different values from a table.

ie. textbox 1 =Sum([Totalinvoice])
textbox 2 =Sum([Totalpayments])

in order to check if the invoices are paid or not and what is the amount of them, i created two more textboxes in order to make some mathematical calculations ie.

textbox 3 =[textbox 1]-[textbox 2]
textbox 4 =[textbox 2]-[textbox 1]

So, both text boxes 3,4 are giving the same number with a different sign depending the sum of of textboxes 1, 2(for example textbox3= 1.000,00 and textbox4= - 1.000,00)

Up to this point everyhting is ok, but

i want to show only the positive numbers in textbox 3 and textbox 4 whenever this occurs. How do i achieve this ?

I would think that a simple IIf Statement would work.

textbox 3: IIf(([textbox 1]-[textbox 2])<0, "", ([textbox 1]-[textbox 2]))
textbox 4: IIf(([textbox 2]-[textbox 1])<0, "", ([textbox 2]-[textbox 1]))

 

[sviggos]

thank you missingling for your quick reply though it did not work since it is still there. maybe this is because it is a calculated value and not an existing in a table value

 

[boblarson]

Why not simply use the ABS function?

 

[MSAccessRookie]

Why not simply use the ABS function?

I interpreted the requirement to be:

• Show values >= 0
• Hide Values < 0

 

[boblarson]

I interpreted the requirement to be:

• Show values >= 0
• Hide Values < 0

Could be - hopefully the OP will qualify.

 

[missinglinq]

I made the same assumption as the Rookie, but the original question wasn't terribly clear. Assuming the OP doesn't want to show anything if the value is negative, using Conditional Formatting as I suggested should work; it did in a form I set up using the OPs scenario.

And here's wishing everyone a Happy New Year!

Linq

 

[sviggos]

I interpreted the requirement to be:

• Show values >= 0
• Hide Values < 0

could you please be more specific?

 

[boblarson]

could you please be more specific?

actually, we were asking you to be more specific. Several of us read your request in different ways so we want you to clarify what you were really asking for.

 

[sviggos]

I made the same assumption as the Rookie, but the original question wasn't terribly clear. Assuming the OP doesn't want to show anything if the value is negative, using Conditional Formatting as I suggested should work; it did in a form I set up using the OPs scenario.

And here's wishing everyone a Happy New Year!

Linq

i will try this also to a new test database and come back with results

 

[sviggos]

actually, we were asking you to be more specific. Several of us read your request in different ways so we want you to clarify what you were really asking for.

the main idea was to show only the values that are positive in a field and hide the ones that are negative. Sorry if i confused you, though i tried ones again the idea of missingling and it worked!! What i have not did in the first time was to apply the conditional formatting to the specific field!!! So, thank you for your help!!
As for the exression of Accessrookie, i am still wondering if it works with the iif() function. if you have the time please let me know.

 

[boblarson]

That's the benefit of having several people look at something. We all sometimes read it in a different way and that can be beneficial because we can come upon the answer more quickly that way if we're all submitting things based on our understanding. Unfortunately sometimes that confuses the person trying to use it. So, it is good that you have figured it out and Linq, of course, is great at putting good solutions out there.