Data type changing after multiplying two columns in MS Access

I want to multiply two columns with each other and use the following code

CDbl(amount) * CDbl(weighting)
AS [amount_weighted],

“Amount” is a regular number while “weighting” is a value between 0 and 1. The resulting column is formatted as “short text” in Access, although it should be a number. Occasionally the column “weighting” or “amount” can be empty, may this be a reason for the malfunctioning? The wrong formatting gives causes a “number stores as text” error when I want to export the resulting column into Excel.

Answer

The fact that single lines were empty caused the Problem.

nz(amount,0) * nz(weighting,0) as [amount_weighted]

Using this instead of CDbl and therefore inserting “0” into the empty lines solved the problem.