Is CDec() neccesary ?

  • Thread starter Thread starter Albert
  • Start date Start date
A

Albert

Hi

In VBA, when I use decimal data type field from recordset as a variable in
arithematic expression or logical expression, Do I have to use CDec() along
with it ?

Example 1 ;
If CDec(tblA!Amt) > 5000 then
or following is enough
If tblA!Amt > 5000 then

Example 2 ;
Dim TaxAmt as Variant
TaxAmt = CDec(tblA!Amt) * 0.1
or following is enough
TaxAmt = tblA!Amt * 0.1

Thanks
 
It's a good habit to get into, as far as I'm concerned. Access will usually
do the coercion of variable types for you, but there are some times when it
doesn't. Future versions, though, may insist that you explicitly do the
conversions (If I'm not mistaken, .Net does force you)
 
Explicit typecasting is a good idea. Although VBA is very loosely typed, you
can (and should) specify your types when it matters.

However, it is generally not necessary to wrap fields in CDec() as in your
first example, since fields already have a data type. To find out what it
is, open tblA in design view, and select the Amt field. What is its data
type? Currency? Number? If number, what is the field size (lower pane)?
Decimal? Double? Long?

In the code:
If tblA!Amt > 5000
tblA!Amt will be whatever data type the field is.
5000 will be an Integer (the default type).
VBA will typecast one or the other to make a comparison.
If Amt is a Decimal field, and you want to guarantee the comparison of two
decimals, you would code:
If tblA!Amt > CDec(5000) Then

In your 2nd example where you are storing a variable, there may be a valid
reason why you want to Variant to be of subtype Decimal. However, you must
consider the possibility that the field could be Null. CDec() can't handle
nulls, so you probably need:
TaxAmt = CDec(Nz(tblA!Amt, 0)) * CDec(0.1)

To ask VBA how it understands the data type of something, use the TypeName()
function, e.g.:
? TypeName(5000)
? TypeName(0.1)
? TypeName(tblA!Amt * 0.1)
 
Back
Top