find and replace decimals to percentages 2

[misscrf]

Sorry if a similar post is up, but the keyword searching is under maintenance.

I have a table that has a field which looks like this:

98%
97%
99%
96%
0.98
0.99
0.97
0.97
etc

I need to find the ones that are 0.** and make them **%
Is there a good method for doing this?


misscrf

Management is doing things right, leadership is doing the right things

 

[SkipVought]

This is just a formatting issue, just Format in your query results of Form to display percent.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[misscrf]

I need to change it in the table though.

misscrf

Management is doing things right, leadership is doing the right things

 

[PHV]

What is the data type of this field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Why? a number is a number. the VALUE is no different! Its just a table, not a report.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lespaul]

First run this query and see if you get the correct info:

SELEcT fldName, Right(fldName, 2) & "%" FROM tblName WHERE InStr(".", fldName) > 0

if that returns the correct information you can use an update query to correct them:

UPDATE tblName SET fldName = Right(fldName, 2) & "%" WHERE InStr(".", fldName) > 0

Leslie

 

[misscrf]

There is text in some of these fields, and it has to be updated in the table, because that it what I was told to do. I don't know why, but they need it done.

I don't ask questions that wont change the answer lol.

Thanks, Leslie, I will try that and see how it goes.


misscrf

Management is doing things right, leadership is doing the right things

 

[lespaul]

(I may have the arguments reversed for the functions, check the help!)

leslie

 

[PHV]

I need to find the ones that are 0.** and make them **%
UPDATE yourTable SET yourField=Right(yourField,2) & '%'
WHERE Left(yourField,2)='0.'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

There is text in some of these fields, and it has to be updated in the table, because that it what I was told to do."

That was a pertty important piece of information. Most of us would assume that a table would be designed to have numeric data in numeric fields and NOT in TEXT fields.

Manipulating NUMBERS and TEXT take DIFFERENT functions.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[misscrf]

yeah, I am finding this out. This is a situation where if I can't figure out a way to automate it, I will have to go through and manually change them all. Not really wanting to do that.

Any suggestions, please let me know.

misscrf

Management is doing things right, leadership is doing the right things

 

[SkipVought]

Suggestion: Correct the table format and data!

What Text data is in this field and what does it mean?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[misscrf]

the column is grade. It has pharmacutical information in the table so here is a further example of the data:

grade
98%
97%
98%
98%
97%
98% Predominantly Trans
98%
98%
High Temperature
90% A.C.S. Reagent
0.99
0.9995
0.97
0.96


So as you see the field is in necessity, a messy text field, and we just happen to need to replace conditional data within it...

Now any suggestions? lol.

Thanks for all of the responses.


misscrf

Management is doing things right, leadership is doing the right things

 

[SkipVought]

"...and what does it mean?" Maybe you need another field for Comments or something else.

Are you ever going to use these values for coputations? If not, then they are just notations that you can correct today (cosmetic modifications), but tomorrow, someone will enter something that may have to be FIXED -- JUST for cosmetic reasons!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[PHV]

In the standard code module paste this code:

Public Function setPercent(myField)
If myField Like "0.##*" Then
  myField = 100 * Val(myField) & "%" & Mid(myField, InStr(myField & " ", " "))
End If
setPercent = myField
End Function

Then you may try this:
UPDATE yourTable SET grade = setPercent(grade)
WHERE Left(grade,2)='0.'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244