The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
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:
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
Asked:
Who is Participating?
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
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
' 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
Never handle numbers as text. Thus:
Apply the percent format for display only.
/gustav
If gp >= 0.15 And gp < 0.2 Then
Apply the percent format for display only.
/gustav
gustav...that does ok until a person enters 14.99 or any number with a . it is rounding it off ? .15
pat:
so doing something like this is not working
dim gp as double
gp = Format(Me.TARGET_GP.Value / 100, "0.00%")
dp
so doing something like this is not working
dim gp as double
gp = Format(Me.TARGET_GP.Value / 100, "0.00%")
dp
Always use Currency for amounts. Then to round to two decimals:
Rounding values up, down, by 4/5, or to significant figures
/gustav
gp = CCur(Format(Me!TARGET_GP.Value, "0.00"))
gp = CCur(Format(Me!TARGET_GP.Value, "0.0000"))
Rounding values up, down, by 4/5, or to significant figures
/gustav
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.
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.
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
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
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)?
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)?
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.
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.
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
Pat, applications in the business are also built around these tables. So it is a trickle effect. Cost!
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.
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
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