Calculate numbers from a text field?

[bugleboy]

Ok, this one has me stumped.

I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.

How do I apply the calculations on the field choices if the field properties have to be text instead of number?

Thanks,

bugleboy

 

[llkhoutx]

Use the appropriate convert function, as in, Cdbl (convert to double), Cint (convert to integer), etc. Use the Access Help menu to search on "Cint function" wihtout the quotes.

dblResult = cdbl(Me!TxtField1) * cdbl(me!txtField2)

etc.

 

[Moniker]

This function will return just the numeric part of any string (I.e., pass it "High probability = 9 points" and it will return "9"). Note that using Cdbl or CInt or even Val will not work on a string like yours, which is why I wrote this function. (CDbl and CInt will error out, and Val will return a zero.)

Function NumbersOnly(strConvert As String) As String
'Returns only the numbers from a passed string. 

    Dim curChar As String
    Dim ctr As Integer
    
    If IsNull(strConvert) Then
        NumbersOnly = ""
        Exit Function
    End If
    
    For ctr = 1 To Len(strConvert)
        curChar = Mid(strConvert, ctr, 1)
        If IsNumeric(curChar) Then
            NumbersOnly = NumbersOnly & curChar
        End If
    Next

End Function

Note that it's still returning in String format, so apply the conversion afterward. Sample code would look like this:

Your_Variable_Name = CInt(NumbersOnly("High probability = 9 points"))

Note that you should replace the "High probability = 9 points" in the above code snippet with the field name in your table. That will automate the process and allow you to cycle through all the records.

~Moniker

 

[bugleboy]

This forum rocks!

Thanks all!!!!

I will try to implement your ideas today. I'm sure I'll have more questions after I get into it, like where to place the functions....

bugleboy

 

[Moniker]

For the location of the function, just put it in a module not attached to a form. Press Alt-F11 to open the VB Editor To the top left, open the Modules folder (if it's not already open). If you have no modules, right-click the Modules folder and Insert a new module. Name it something obvious like "modConversionFunctions". Then just copy/paste the function I provided into that module. That will make NumbersOnly function (and anything other function or subroutine you place in the module) available to all queries/forms/macros.

It's best practice to group your functions and subroutines by what they do. For example, if you make a function like my NumbersOnly function and make another called CharactersOnly, then both are text conversion functions and belong in the same module. However, if later on, you make a function that, let's say, checks what recordsets are open, then that should be in it's own module (something like "modDataFunctions").

Yes, you can put all your functions and subroutines in one module and it will work, but you'll find it much easier to maintain when you logically separate things.

Finally, while still in the VB Editor, go to Tools -> Options, and make sure "Require Variable Declaration" is checked on the Editor Tab. This will automatically place "Option Explicit" at the top of each module. What this does is force you to declare every variable you use. It's a small extra step to save some potential major headaches. Without this, you can just make up variable names on the fly. There are a lot of reasons to not do this. The major two are:

1) Since you don't Dim the variables as a specific type, they are defaulted to Variant, the slowest, most space consuming variables you can have.

2) You can easily use a variable twice on accident, which will cause all sorts of headaches. For example, if you make code like this (without first declaring X as the variable):

For X = 1 to 10
...<code here>
Next

and then later in the same function or routine, you reference X again, it's value is preset to 10.

Just Dim every variable you need at the top of each function/routine you use (unless they're global), and you avoid these pitfalls.

~Moniker

 

[Bat17]

more properly, you should be storing the numbers and using a lookup table to supply the text. Then your list box can be bound to the number field using a hidden first column whilst displaying the text to users.

Hope that makes sense

Peter

 

[bugleboy]

Thank you Moniker for the great primer in functions. I am anxious to try it.

I'd also like to get more info from Bat17 on his approach. Can you elaborate Bat17?

Thanks to you both,

bugleboy

FYI, my boss thinks I'm an Access whiz thanks to you guys!

 

[Bat17]

have a look at this sample

Peter

 

[bugleboy]

Got it!

That makes perfect sense! Now I just need to go modify my main table and form.

My next challenge is to autopulate a fourth field based on where the txtRisk result falls in a number range, but it should be fairly straightforward using >0<15 = Low, >14<29 = Medium, >28<49 = High, or something like that.

Thanks Bat17!

 

[Moniker]

Your fourth field is a calculated field, like this:

FourthFieldName = Switch([txtRisk]>0 And [txtRisk]<15,"Low",[txtRisk]>14 And [txtRisk]<29,"Medium",[txtRisk]>28 And [txtRisk]<48,"High")

Switch works like a big nested IF statement but without a FALSE component, formatted like this:

Switch([condition1],[result_if_condition1_is_true], [condition2],[result_if_condition2_is_true],
[conditionx],[result_i_conditionx_is_true])

The Switch function stops running once a condition is met (is true). If condition1 is met, condition2 through conditionX are never looked at. If condition1 is not met, then condition2 is checked, and so on.

The above example would return a NULL if [txtRisk] was not >0 and <48. If you do not want to return a NULL, add a True check, like this:

FourthFieldName = Switch([txtRisk]>0 And [txtRisk]<15,"Low",[txtRisk]>14 And [txtRisk]<29,"Medium",[txtRisk]>28 And [txtRisk]<48,"High",True,"No Match")

~Moniker

 

[bugleboy]

Yeah!

Guys,

I got it to work! I used Bat17's table idea and Moniker's Switch idea and it works perfectly!

I shouldn't be enjoying this as much as I am

You guys are great!

bugleboy