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.
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.