To calculate fields that have 'Number' & 'Text' datatype.

  • Thread starter Thread starter NJ
  • Start date Start date
N

NJ

I am trying to calculate a form entry where some of the fields values have
'Number' datatype while some have 'Text datatype.
 
In your text field, is the data always numerals or are there some ABCs in it
also?

A representative sample of the data and what you are trying to do would help
us to help you.
 
I am trying to calculate a form entry where some of the fields values have
'Number' datatype while some have 'Text datatype.
Click to expand...

You can use the Val() function to convert a text string containing a number to
an actual numeric value: e.g.

Val("123") = 123
Val("35ABC") = 35

But be warned:

Val("ABC123") = 0 (since it's not a leading number)
Val("123E4ABC") = 1230000 (the E4 is interpreted as scientific notation)
 
Thank for your eamil:

The text field is a free form entry textbox where users can enter either 1
or 0 or N/A; but if I do the calculation; then it's the problem, as in the
other free form textboxes users are allowed to enter only either 1 or 0.

The form is something like the below:

Trb_Summy Trb_Desc CTI CC Vendor
Outage Score
(only 1 or 0) (only 1 or 0) (only 1 or 0)(only 1 or 0)(only 1 or 0 or N/A)
(only 1 or 0 or N/A)

The Score is the calculated field where I use the below formula:

Private Sub Score_KeyDown(KeyCode As Integer, Shift As Integer)
If (Outage = "N/A") Or (Vendor_Tab = "N/A") Then
Score.Value = (((Trb_Summy + Trb_Desc + CTI + CC + Vendor + Outage) / 9) *
100)
Else
Score.Value = (((Trb_Summy + Trb_Desc + CTI + CC + Vendor + Outage) / 10) *
100)
End If
End Sub
 
Thank for your eamil; actually I am trying to do the below:

The text field is a free form entry textbox where users can enter either 1
or 0 or N/A; but if I do the calculation; then it's the problem, as in the
other free form textboxes users are allowed to enter only either 1 or 0.

The form is something like the below:

Trb_Summy Trb_Desc CTI CC Vendor
Outage Score
(only 1 or 0) (only 1 or 0) (only 1 or 0)(only 1 or 0)(only 1 or 0 or N/A)
(only 1 or 0 or N/A)

The Score is the calculated field where I use the below formula:

Private Sub Score_KeyDown(KeyCode As Integer, Shift As Integer)
If (Outage = "N/A") Or (Vendor_Tab = "N/A") Then
Score.Value = (((Trb_Summy + Trb_Desc + CTI + CC + Vendor + Outage) / 9) *
100)
Else
Score.Value = (((Trb_Summy + Trb_Desc + CTI + CC + Vendor + Outage) / 10) *
100)
End If
End Sub
 
Actually the Val function works.

Thanks

John W. Vinson said:
You can use the Val() function to convert a text string containing a number to
an actual numeric value: e.g.

Val("123") = 123
Val("35ABC") = 35

But be warned:

Val("ABC123") = 0 (since it's not a leading number)
Val("123E4ABC") = 1230000 (the E4 is interpreted as scientific notation)
Click to expand...
 
Back
Top