Why does this query change my numeric data to text

[azurekca]

I'm running a make table query that is converting my numeric data into text and I need it to remain numeric. The two input numeric fields are set to Number/Double.

USE_Number: Switch(Not IsNull([NumericField1]),[NumericField1]*0.1,Not IsNull([NumericField2]),[NumericField2]*0.001)

This is giving me the correct results, Nulls where neither number is available, and the correct math where the number is available, but unfortunately results in a text field.

I tried adding CDbl, and this results in a numeric field but where neither Number is available the result is #ERROR and I need it to be Null

USE_Number: Round(CDbl(Switch(Not IsNull([NumericField1]),[NumericField1]*0.1,Not IsNull([NumericField2]),[NumericField2]*0.001)),2)

Any help on this would be really appreciated! Thanks!

[June7]

CDbl and other conversion functions cannot handle Null. Will have to return a value such as 0 in place of the Null. Use Nz() function.

[azurekca]

Thank you, I realized that. As I don't want zeros in my resulting table, I was hoping to find another solution.

[June7]

Maybe build a query that does that calc then use that query as the source for MAKE TABLE. Use this expression for the Update To parameter:

IIf([USE_Number]=0, Null, [USE_Number])

Or could then run an UPDATE sql and change all the zeros to Null.

Why do you need to run MAKE TABLE?

[azurekca]

Thank you June7, I tried a modified version of your suggestion and it's working now!

I used: IIf(IsNull([NumericField1]) AND IsNull([NumericField2]), Null, Round(CDbl(Switch(Not IsNull([NumericField1]),[NumericField1]*0.1,Not IsNull([NumericField2]),[NumericField2]*0.001)),2))