number vs text data type

[Guest]

I have a field in a table which has text data type but it contains only
numeric values in all records. I observe that I can add these values and
perform other calculations with these values in this field just as if it were
a number field. I was wondering what is the advantage of defining a field as
numeric if even numeric values in text fields can be mathematically operated.
My understanding of a text field was that any value in there is just a string

 

[Van T. Dinh]

Access probably type-casts the texts to numeric values silently. However,
you cannot rely on automatic type-casting 100%. As an example, type the
following in the Immediate / Debug window:

? "12" + "23"

and the result will be "1223" and not 35!

If the values are numeric & I need to perform calculations, I always use
numeric Fields. If the values are Text & I only *manipulate* these values,
I use Text Fields.

 

[Guest]

I would have preferred using number data type myself for numeric values field
but I am facing a problem here: I have a calculated field say CalFld =
Fld1-Fld2*Fld1/100. When I define Fld1 and Fld2 as number data type with
field size as single (they have 2 decimal places), CalFld comes out with 2
decimal places as desired in some rows and as an exponential value in other
rows eg. 2.345675E-6. (Why?) Whereas with the fields defined as text type,
CalFld comes out "clean". I am doing simple arithmetic operations so I am
hoping that would count as "manipulating" values.

 

[Albert D.Kallal]

The fact that you have expression problem is exactly WHY you want to use a
number field.

Try running the following code:
Public Sub TestAdd()


Dim MyNumber As Single
Dim i As Integer


For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub


Here is the actual outpput of the above:


1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 additions..already rounding is occurring


and if we add the following line of code to the end of the above:


if MyNumber = 10.1 = True then


msgbox "the number is 10.1"


else
msgbox "the number is something else"
endif


The above will actually produce:


the number is something else


The problem is that "real" numbers are only approximate, and thus for any
financial calculations, you need to use whole, or integer numbers.

That means you should use the currency type field. It gives a precisions to
4 decimal places, and is ideal for financial stuff.

2.345675E-6

That is 0000.234567

It looks like some round problem has occur here.

If you need more then 4 decimal places, then you can / should try using
double size.