issue with conditional foirmatting and code reading a string as a numeric

access 2010 vba
sql server

I have a linked that from sql server that has a field  that is stored as a string unfortunately..TARGET_GP

"Line_Color" IS STRING FIELD ALSO , BUT JUST GETTING UPDATED depending on the code, not conditional formatting

So when conditional formatting is looking at the value it does not evaluate correctly when the value is between 0.01 and 9.99
or the code below:

Dim gp As String
  

    If Len(Nz(Me.TARGET_GP, "")) = 0 Then
   Exit Sub
End If
Me!TARGET_GP = Replace([TARGET_GP], "%", "")
Me!TARGET_GP = Replace([TARGET_GP], "..", ".")
If IsNumeric(Me.TARGET_GP) = True Then
    Me.TARGET_GP.Value = Format(Me.TARGET_GP.Value / 100, "0.00%")
End If


If Me!TARGET_GP = "%" Then
Me!TARGET_GP = ""
End If


gp = Me.TARGET_GP.Value



Me!Line_Color = ""


If gp <= "14.99%" Then
  MsgBox "Sorry But the Target_Gp is Below Threshold. Please Correct.", vbCritical, "TARGET THRESHOLD"
  LINE_GP_FAILS = False
  Me.Line_Color = "RED"

End If

' red Light
If gp >= "15.00%" And gp <= "19.99%" Then
  '  MsgBox "RED dave"
    LINE_GP_FAILS = False
    Me.Line_Color = "RED"

End If

' Yellow Light
If gp >= "20.00%" And gp <= "24.99%" Then
  ' MsgBox "yELLOW"
   LINE_GP_FAILS = False
   Me.Line_Color = "YELLOW"

End If

' green light
If gp >= "25.00%" Then
 ' MsgBox "gREEN"
  LINE_GP_FAILS = False
  Me.Line_Color = "GREEN"

End If
   
   
   Exit Sub

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I am a bit lost because i can't see where is the issue...can you give us a sample of the problematic value to start...i think that after you do the % and .. replacements to convert them to double (cdbl) and then do the checks
0
FordraidersAuthor Commented:
if i type in the cell  3 the color should be red....it is getting to this line and

' green light
If gp >= "25.00%" Then
 ' MsgBox "gREEN"
  LINE_GP_FAILS = False
  Me.Line_Color = "GREEN"

End If
turning the cell green...anything .01 to 9.99  

Example: in debug
if gp = "3.oo%"  
it should not accepting this "IF statement.
it is saying gp >= 25.00%  but it is not ?

It should be evaluating this line correctly:
If gp <= "14.99%" Then
  MsgBox "Sorry But the Target_Gp is Below Threshold. Please Correct.", vbCritical, "TARGET THRESHOLD"
  LINE_GP_FAILS = False
  Me.Line_Color = "RED"

End If

because gp <= "14.00%"


dp
0
Gustav BrockCIOCommented:
Never handle numbers as text. Thus:

If gp >= 0.15 And gp < 0.2 Then

Open in new window

and so on an so forth.

Apply the percent format for display only.

/gustav
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FREE DOWNLOAD
John TsioumprisSoftware & Systems EngineerCommented:
Like i said you should treat the input as decimals and work with them as gustav advised...
0
FordraidersAuthor Commented:
gustav...that does ok until a person enters 14.99 or any number with a  .  it is rounding it off ?  .15
0
Gustav BrockCIOCommented:
That's up to your form and the data type of the field.

/gustav
0
John TsioumprisSoftware & Systems EngineerCommented:
take a look here....i am not sure about your decimal separator.... Clipboard02.jpg
0
PatHartmanCommented:
Numbers stored as strings will act like strings. 10 will be less than 9 because 1 is less than 9 and the characters are compared one at a time, left to right.

If you have something that is truly a number and needs to be treated as such, then you need to store it as a number or convert it to one every time you use it.  Of course the conversion will fail if there are non-numeric characters in the string.

Numeric values that require decimal places can be stored as single or double depending on the size of the value or as currency which is my personal favorite as long as I don't need more than four decimal digits.  Currency avoids the issues of floating point errors.  Don't confuse Currency the data type with Currency the format.  The Currency data type can be formatted as any type of number including percent.

Integer and Long Integer do not support decimal places.  They are always whole numbers.  If you tried to enter 14.99, it would be saved as 15.

If you use single, double, or currency and specify percent as the format.  14.99 will be saved as .1499
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
got this to work finally

Me.TARGET_GP.Value = Format(CDec(Me.TARGET_GP.Value) / 100, "0.00%")

dp
0
FordraidersAuthor Commented:
sorry did not work  Me.TARGET_GP.Value = Format(CDec(Me.TARGET_GP.Value) / 100, "0.00%")
0
FordraidersAuthor Commented:
pat:
so doing something like this is not working
dim gp as double
 
 gp = Format(Me.TARGET_GP.Value / 100, "0.00%")    

dp
0
Gustav BrockCIOCommented:
Always use Currency for amounts. Then to round to two decimals:

gp = CCur(Format(Me!TARGET_GP.Value, "0.00"))

Open in new window

or four: 

gp = CCur(Format(Me!TARGET_GP.Value, "0.0000"))

Open in new window

Or use my excellent rounding functions:

Rounding values up, down, by 4/5, or to significant figures

/gustav
0
PatHartmanCommented:
1. I specifically did not include the Dec datatype.  It has issues.
2. Once you Format() a value it becomes a STRING and that's your original problem.  It is no longer a number.  I NEVER suggested using the Format() function.  Always use the format property if you want to control how something "looks" to humans.
0
FordraidersAuthor Commented:
Is the Conditional formatting rules...reading the   .text    or   .value     in a subform  field ?


I have redone my conditional formatting to reflect the "gp"  value in the field   WITHOUT THE % sign displaying

Gustav, so my only  challenge now is displaying the % and apply conditional formatting.
Because the % sign causes the evaluation to not work  




dp
0
PatHartmanCommented:
It is using the value property.  

There is no challenge displaying the % if you use the Format PROPERTY as long as the bound value is numeric.  Have you modified the tables so that the value is actually defined as numeric (preferably as Currency)?
0
FordraidersAuthor Commented:
No, dba wont let me. As far as defining a new datatype field.
0
PatHartmanCommented:
You have a DBA that won't let you change an invalid data type?    I'm sure he thinks he has a very good reason.  Is his name Derek?  Mine has the same idiotic attitude even though I proved to him that Excel has the same problem as Access so EVERYONE was seeing bad data and having to compensate.  He didn't want to break anyone who had compensated.  So year in and year out people have to compensate for the stupid decision.

Ask him to solve your problem with a view.  Leave the string field as it is since that is what you will need to update but ask him to create a new, numeric version of the column.  The view will NOT impact any other application.   It will be a pain to have to work with two fields but easier than to try to make a single field be both numeric and a string at the same time.
0
Gustav BrockCIOCommented:
Because the % sign causes the evaluation to not work

But percent sign isn't used when evaluation values. It is for formatting the display only.

/gustav
0
FordraidersAuthor Commented:
Pat, applications in the business are also built around these tables. So it is a trickle effect. Cost!
0
FordraidersAuthor Commented:
How do format for display purposes only then . ?
0
Gustav BrockCIOCommented:
Use the Format property of the control (TextBox).

/gustav
0
PatHartmanCommented:
Did you ask the DBA to make a view for your use?

Having a single control formatted two ways at the same time isn't possible.  If you leave the control formatted as text, you need to get rid of the non-numeric characters so you can convert it to a number for internal use.
0
FordraidersAuthor Commented:
Thanks for all the advice !..very helpful..as always
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.