Query expression builder returns text instead of numeric

[B+ve]

When I build a simple expression in a querry based on two other fields, both
numeric, the result is a text/Date value. e.g., Expr1: [Value]+[Commission]
When I try to Format the field in Properties no formats are available and
the Input Mask is for Text?date.

I have even tried to force format to numeric and also used the FormatNumber
function in the expresion builder, but to no avail.

 

[Allen Browne]

Try something like this:

Expr1: CCur(Nz([Value],0) + Nz([Commission],0))

 

[B+ve]

Thanks Allen, that worked.
Still puzzeled why the expression would turn two numeric fields into a
string, any idea?
--
Be positive

Try something like this:

Expr1: CCur(Nz([Value],0) + Nz([Commission],0))


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

When I build a simple expression in a querry based on two other fields,
both
numeric, the result is a text/Date value. e.g., Expr1:
[Value]+[Commission]
When I try to Format the field in Properties no formats are available and
the Input Mask is for Text?date.

I have even tried to force format to numeric and also used the
FormatNumber
function in the expresion builder, but to no avail.

 

[Allen Browne]

Ask Access what's going on.

Open the Immediate Window (Ctrl+G), and enter things such as:
? CurrentDb.QueryDefs("Query1").Fields("ID").Type

The number will be one of the DAO decimal values from here:
http://allenbrowne.com/ser-49.html
e.g. 4 for Long, 5 for Currency, 10 for Text, or 9 for Binary (typically
where JET can't determine the type.) If it helps, wrap your expression in
FieldTypeName() from here:
http://allenbrowne.com/func-06.html

If the fields are directly from a JET table (stored in Access), it should be
able to determine the type. If the fields are expressions in a lower-level
query, or if they are attached from a source that has no specified data type
(e.g. xls), it has to guess.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, that worked.
Still puzzeled why the expression would turn two numeric fields into a
string, any idea?
--
Be positive

Try something like this:

Expr1: CCur(Nz([Value],0) + Nz([Commission],0))

When I build a simple expression in a querry based on two other fields,
both
numeric, the result is a text/Date value. e.g., Expr1:
[Value]+[Commission]
When I try to Format the field in Properties no formats are available
and
the Input Mask is for Text?date.

I have even tried to force format to numeric and also used the
FormatNumber
function in the expresion builder, but to no avail.