divide numbers in two text boxes where values can contain a character e.g. an asterisk

[Riyad.elkhoury]

Hello Everyone I am facing a small problem in access and I hope that someone will be able to help.

I have a form with 3 sections. In the First section I have different text boxes where I add values of enzymatic activities. In the second section I have one text box to which i add a reference value and the last section I have text boxes (calculated fields, which are bound fields) that divide each activity from section one by the reference activity from section 2.
In section one, if the value is outside the reference range, I added a small vba code on "after update property" to add an asterisk to the value. for instance, if the value that I have added is 4 (ref range is 5-9) then 4 becomes 4*.
My problem is the calculated field in this cases gives me the following error : #Type!
How can I overcome this issue by keeping the calculated field as bound field to preserve the values in the corresponding table?



thank you for any help

[CJ_London]

use the val function

so if myfield=4*

val(myfield) will return 4 as a number

[Riyad.elkhoury]

use the val function
so if myfield=4*
val(myfield) will return 4 as a number

Thank you Ajax for your reply but this is not what I why really want.

I need to maintain the values that are outside the range with an asterisk those will appear on a report with an asterisk indicating that they are abnormal values. To have the asterisk in the activity fields I used the following code :
Private Sub CICoQ1_AfterUpdate()
If Me.CICoQ1.Value < 10.42 Or Me.CICoQ1.Value > 47.3 Then
Me.CICoQ1.Value = Me.CICoQ1.Value & "*"
End If
End Sub
On the report will also appear the calculated field that is dividing the activities by the reference and here is my problem. When there is an asterisk in the activity field or the reference one the calculated field gives an error.
So, I was wondering if there is a way to add a code in the calculated field in order to ignore the asterisk if there is any while doing the division operation.

thank you again

[CJ_London]

So, I was wondering if there is a way to add a code in the calculated field in order to ignore the asterisk if there is any while doing the division operation.

that is what the val function does

if calculation= val("4*") * 5 it will calculate a value of 20

however from this

If Me.CICoQ1.Value < 10.42 Or Me.CICoQ1.Value > 47.3 Then
Me.CICoQ1.Value = Me.CICoQ1.Value & "*"

assuming the CICoQ1 control is bound to a field then that field has to be text, it cannot be double or text as you feel like

[Riyad.elkhoury]

Ill be more explicit,

CICoQ1 = Activity field (bound to a table; Data type: text). lets consider it = 20* (I want to keep it in this format in order to have it in the report with the asterisk).

CS = Reference field (bound to a table; data type: text). lets consider it = 80.

CICoQ1/CS = Calculated field (bound to a table; data type: Calculated). If, in this field (in the form) i add = val(CICoQ1) / val(CS) I obtain the result of 20*/80 but I lose the bound. The obtained value is not added to the table.

so I tried to modify the source. For that, I used the same code = val(CICoQ1) / val(CS) in the CICoQ1/CS field in the table. I got this message "you cannot use val in a calculated field".

Thank you again.

[John_G]

Calculated field (bound to a table; data type: Calculated).

Values in a calculated field are determined at the table level - you cannot assign values to them in a form or VBA. Change the data type of that field to Double and it should work. Since it is a bound file, to will need to use VBA to assign the value to it.

[davegri]

You don't need the TABLE field CICoQ1/CS. You can always calculate it on the fly.
In your form the textbox holding the calculated result would be
Private Sub Form_Current()
txtResult = CICoQ1 & "/" & CS
End Sub

This form control should be LOCKED, as users should not change it.

You can use the same sort of calculation for a report, by putting the calculated field in a query.

[rpeare]

I think you're making this way harder than it needs to be, if you have defined boundaries of acceptable measurements (tolerances) storing the value with a * is pointless, you can display the value with a * on a form or a report as a matter of formatting.

This mix of trying to evaluate strings as numbers will only cause you grief as you progress with database design. I would store the values as numbers and do the formatting with a * on your forms/reports.

If you are determined to continue with this design you might try

=cdbl(replace(cicoq1, "*", ""))/cdbl(replace(CS, "*", ""))

This will only work if * is the only non numeric character in your field.