Changing Text Color for Negative numbers using VB Code

[gold007eye]

I have dollar amounts in my database and I need it to do something like this

If amount = $0.00 text should be Black
If amount is Greater than $0.00 text should be Blue
If amount is Less than $0.00 text should be Red

Here is what I have tried, but only keeps the 1st coded color. I have this set in the "OnLoad" event on my Main Menu.

If [Current Balance] < "0" Then
[Current Balance].ForeColor = vbRed
ElseIf [Current Balance] > "0" Then
[Current Balance].ForeColor = vbBlue
ElseIf [Current Balance] = "0" Then
[Current Balance].ForeColor = vbBlack
End If

 

[RuralGuy]

It is hard to believe the something called CurrentBalance is a text field. Try removing the quotes. Why aren't you using Conditional Formating?

 

[gold007eye]

I tried it without the quotes and it didn't work at all. It is a financial database. A query is used to populate this field so that when the main menu is opened it shows the current bank balance.

I am not an expert at AcessDB.. Conditional Formatting?

 

[RuralGuy]

...and it didn't work at all.

Did it show an error of some sort? ac2000 and > has Conditional formating. Right click on the field and go down to "Conditional Formatting...". If in fact your data is text then > and < do not work as you think. You will have to convert the text to a number for your testing to work with maybe CCur.

 

[gold007eye]

It gave no errors at all. Just left the original color I had set the text box to be. (with quotes) without quotes it changed the color to the first color I had listed in the code and ignored the rest of the code.

 

[RuralGuy]

So without quotes is changes something? It sounds like a numeric field to me! If you are not going to use Conditional Formatting the move your code to the Current event of the form and leave off the quotes. I expect you will get more satisfactory results.

 

[gold007eye]

Grr.. Still no luck. I created an uncontrolled text box formated as Currency on the main menu that gets the dollar amount from a query that is run when the main menu loads. Changing the color seems to work as long as I don't want to change the color to a different color using a variable. I am not sure what to do now?

 

[RuralGuy]

Are you using Conditional Formatting? What code do you try when you use a "variable"? What event are you using?

 

[Demigod]

How about using the RGB function?
Black = RGB(0,0,0)
Blue = RGB(0,0,255)
Red = RGB(255,0,0)

I have no idea if it will work...

 

[gold007eye]

Where would I work that into my code?

 

[Demigod]

instead of using vbBlack and vbBlue and vbRed, try using

If [Current Balance] < 0 Then
[Current Balance].ForeColor = RGB(255,0,0)
ElseIf [Current Balance] > 0 Then
[Current Balance].ForeColor = RGB(0,0,255)
ElseIf [Current Balance] = 0 Then
[Current Balance].ForeColor = RGB(0,0,0)
End If

like i said though, I have no idea if it will work


Then again, the problem could actually be the OnLoad procedure. I believe that the OnLoad procedure is called before the controls are actually loaded. Technically, it should give you an error if this is true. Correct me if im wrong though....

 

[gold007eye]

No such luck.. still doing the same thing. I was hoping this would have been something easy to do.

In theory what code would you use to change the text color to a different color depending on if the number was a positive or negative number?

 

[Demigod]

Me, personally, I would use basically the same code that you used there, i'd even put the code in the same spot you placed it. Try taking the values that you are checking directly from your table.

If FieldName < 0 Then
TextboxName.ForeColor = RGB(255,0,0)
ElseIf FieldName > 0 Then
TextboxName.ForeColor = RGB(0,0,255)
ElseIf FieldName = 0 Then
TextboxName.ForeColor = RGB(0,0,0)
End If

O wait, I made a mistake, thats what you have already. Try making the name of the field and textbox different.

 

[gold007eye]

Only problem is that I have a query run on form load and a Sum function in the control of the text box. it isn't linked to a table.

 

[Bat17]

Have you looked up conditional Formating? it is made for this sort of work.
>Format>Conditional Formatting... in form view.


Peter

 

[gold007eye]

Thanks that is exactly what I needed to do! It works perfectly now.