Converting TEXT to Number--CHALLENGE QUESTION. 4

[mickier]

Professionals! Take this one on.
I have a field "results" on a form that is a TEXT field. In this field, lab techs enter in their test results for a test. All test results are saved in the same table....regardless of the test type so the test results field MUST be text to account for all the different types of results that will be saved (some Pass/Fail, some letters, some numbers).

I have another field on the form that is a SPEC field and is a NUMBER (single) with X decimal places (varies with customer's specification requirements).

Because this RESULT value comes out on a certification (based on cust spec), the decimal places of the results must match the decimal places of the spec.

I want to take my value from the RESULTS field (text type) and convert it to a number type and then change it to match the number of decimal places in the SPEC field.

Can this be done?

 

[UncleJack]

I must be missing something. If your RESULTS field is a bunch of text, how could you possibly convert it to a number? The Val function converts a text entry like "45" to its numerical counterpart. The Format function can be used to set the number of decimal places. But you obviously can't convert "Test ran OK this time" to a number.

Now, on the other hand, if you have standarized results that can be keyed to a number then it's a simple matching operation, but I didn't get that from your question.

Why can't I understand your question?

 

[GingerR]

unclejack--mickier might be using, i.e.:

iif(IsNumeric([Result]),[Result],null)

something like that.

 

[GingerR]

i made a test table how you describe

Result = text
Spec = Single

i made an update query that updates the Spec field to

IIf(IsNumeric([Result]),CSng([Result]),Null)

here are results (sorry not pretty but you can see it works fine):
Result Spec
Ginger
15 15
31.6 31.6
No
Special
8.157 8.157

dont' know how you are handling updating this SPEC field, perhaps OnChange of RESULT field? in any case you can goof around with the code above in lots of diff ways.

good luck.
g

 

[mickier]

Sorry it isn't clear. It is a bit confusing. I'll try to give an example.

Table: Spec
Custspec ID--text
testID--text
Low Limit--text
High limit--text

Table: Results
sampleID--assigned when sample is logged in
testID--text--related to above
Custspec ID-related to above
Result--text
(these are text because all result numbers will be different...not all have the same significant figures--some are P or F)

Example:
Customer 1 has a requirement for us to test Chloride Content. His spec is 40-60.

Customer 2 has a requirements for the same test, but his spec is 45.5-58.5
The spec requirements have different significant figures.

A sample comes to the lab and the form adds records to the result table for every spec test that must be run on this material.
:record:
SampleID 1, testID 1, Custspec 1, result
SampleID 1, testID 1, Custspec 2, result

The test result ends up being 47.5. The technician types 47.5 into the result field for both records.

However, customer #1 does not want to see 47.5, they want to see the rounded version...48.

Right now, the technician must realize that the result for the first record must be typed in differently. They mess this up a lot.

I want the result to be matched to the same decimal places as the spec.....AFTER the technician enters the result.

Maybe--pick it out, convert it, and put it back in.

Hope this is more clear.
Mickie

 

[GingerR]

i'm working on it. almost done.
just wanna be clear:

1) if i can figure out how many decimal places Low Limit has, then that is what we are looking for, right?

2) are people typing the result into a form?

rounding is insane. i almost got it tho....

 

[mickier]

right!
I just want to match it.

Yes they are entering results into a form.

thanks!!!!!

 

[GingerR]

i got it so it makes wrongly entered with too many decimal places round to a lesser number of dec places (like your example) but still working on other way around, i.e. they type in 30 but it should be 30.00

 

[BoxHead]

Hi, would inString and String Length work to let you know where the decimal point is?


Try

If (InStr(1, Text0, ".", 1)) > 0 Then
Text1 = (Len(Text0)) - (InStr(1, Text0, ".", 1))
Else
Text1 = 0
End If

I put this event on a button and it calculates how many characters follow the decimal in Text0. The result displays in Text1. If there is no decimal, the result is zero.



John

 

[GingerR]

yes i'm using that. it's a bunch of parsing that's for sure.

ok here it is. why dont you make a development copy of your form and table so you can play with it. i'm not going for simply FORMATTING here. so instead I'm making SPEC actually contain text that appears as the correct number you want.

STEPS:
1) i changed the type of SPEC to TEXT in the table otherwise no matter what i do, it cuts of final zeros. I hope this will be ok with you. I dont think it will affect anything that you are doing, i.e. simply printing SPEC or displaying it on the screen. If at any time you need to do calculations with it, just use csng(SPEC) to convert to a number again. anyhow, change SPEC to TEXT: do this in a copy of your table and make sure all the data already in there converts properly.
2) in the form after i did #1, i made the format of SPEC be GENERAL NUMBER, decimal places AUTO. so even tho it's text, it looks like a number.
3) ok--in the form design, for the field RESULT, in the AFTERUPDATE property, choose

[Event Procedure]

. if necessary, you might want to do this also in Low Limit AFTERUPDATE in case that figure gets edited later.
4) paste this in between the SUB and END SUB lines:

Dim sngResult 'Numeric conversion of RESULT (Text to Single)
Dim strLowLimit 'Low Limit
Dim DecPointPlace 'Character place of decimal point in Low Limit
Dim NoOfDecPlaces 'Number of decimal places to go out

Dim FinalCalc 'Final Calculation
Dim DecPointPlaceFinal 'Character place of decimal point in FinalCalc
Dim NoOfDecPlacesFinal 'Number of decimal places FinalCalc has
Dim strFinalCalc 'FinalCalc in str format

'If RESULT cannot be converted to a number, then it is text so quit; otherwise, convert to Single and store it in sngResult
If IsNumeric(Me.Result) Then
sngResult = CSng(Me.Result)
Else
Exit Sub
End If

'If LowLimit cannot be converted to a number, then it is text so quit; otherwise store the numeric equiv in numLowLimit
If IsNumeric(Me.[Low Limit]) Then
strLowLimit = Me.[Low Limit]
Else
Exit Sub
End If

'find decimal point placement in Low Limit
If InStr([strLowLimit], ".&quot = 0 Then
DecPointPlace = Len([strLowLimit])
Else
DecPointPlace = InStr([strLowLimit], ".&quot
End If

'find number of decimal places that LowLimit has: NoOfDecPlace = length of LowLimit - place where the dec point is
NoOfDecPlaces = Len([strLowLimit]) - [DecPointPlace]


'Final Calculation: Rounds sngResult taking into consideration the number of decimal places of Low Limit
'Rounding formula example (to round to two places): (INT((x+.005)*100))/100

FinalCalc = CDbl((Int(([sngResult] + (0.5 * (0.1 ^ [NoOfDecPlaces]))) * (10 ^ [NoOfDecPlaces]))) / (10 ^ [NoOfDecPlaces]))

'The rounding formula will cut off final zeros, so now we have to put them back in if necessary
' (i.e. need 30.00 but got 30)

'Convert FinalCalc to string to determine how many decimal places it has.
strFinalCalc = CStr(FinalCalc)

'Perform same routine as above (on Low Limit) to find out how many decimal places FinalCalc has
If InStr([strFinalCalc], ".&quot = 0 Then
DecPointPlaceFinal = Len(strFinalCalc)
Else
DecPointPlaceFinal = InStr([strFinalCalc], ".&quot
End If

NoOfDecPlacesFinal = Len(strFinalCalc) - DecPointPlaceFinal

'If Final Calc & Low Limit have same number of decimal places, we're done
'Otherwise, add in a decmial point if needed
'Then add in final zero's as characters (text) using STRING function

If NoOfDecPlacesFinal = NoOfDecPlaces Then
Me.Spec = FinalCalc
Exit Sub 'done
Else
If NoOfDecPlacesFinal = 0 Then
strFinalCalc = strFinalCalc & "."
End If
strFinalCalc = strFinalCalc & String((NoOfDecPlaces - NoOfDecPlacesFinal), "0&quot
Me.Spec = strFinalCalc
End If





LET ME KNOW HOW IT GOES
g

 

[mickier]

Ginger is a GODDESS!

 

[GingerR]

so i guess that did it for you huh? no problems making the SPEC field text? anyhow, there might be slicker ways, i dont know, but hey it works right?

thanks for the Goddess Status.
i do a lot of pretty intricate calculations. i like that kinda stuff.

glad to have helped )
see you later--g