Adding & Subtracting Numbers(Fields) with Expression Builder

S

Stuart

This has got to be simple but I don't see it. I have a simple query of a
table and I can see all the fields (defined as numbers in the table) in the
database. I simply want to add and subtract the fields to get a computed
value in the query. Currently the calculated column is always turns out
blank. (e.g.Expr1:
Alphy!Level-Alphy!Upright-Alphy!Chest-Alphy!Storeroom-Alphy!Kitchen or
simply an order amount equal to a desired level minus all on hand amounts in
the database) What is the exact syntax required for an computational
replacement in a query?

TIA, Stuart
 
F

fredg

This has got to be simple but I don't see it. I have a simple query of a
table and I can see all the fields (defined as numbers in the table) in the
database. I simply want to add and subtract the fields to get a computed
value in the query. Currently the calculated column is always turns out
blank. (e.g.Expr1:
Alphy!Level-Alphy!Upright-Alphy!Chest-Alphy!Storeroom-Alphy!Kitchen or
simply an order amount equal to a desired level minus all on hand amounts in
the database) What is the exact syntax required for an computational
replacement in a query?

TIA, Stuart
Click to expand...

Use the dot, not the bang.
Alphy.Level

It's likely one of the fields is null.
Some value + Null = Null.
Use the Nz() function. Look it up in VBA help.

Expr1:Nz([Level],0) - Nz([Upright],0) - Nz([Chest],0) -
Nz([Storeroom],0) - Nz([Kitchen],0)
 
S

Stuart

Thanks Fred. That fixed it.
Stuart//
fredg said:
This has got to be simple but I don't see it. I have a simple query of a
table and I can see all the fields (defined as numbers in the table) in
the
database. I simply want to add and subtract the fields to get a computed
value in the query. Currently the calculated column is always turns out
blank. (e.g.Expr1:
Alphy!Level-Alphy!Upright-Alphy!Chest-Alphy!Storeroom-Alphy!Kitchen or
simply an order amount equal to a desired level minus all on hand amounts
in
the database) What is the exact syntax required for an computational
replacement in a query?

TIA, Stuart
Click to expand...

Use the dot, not the bang.
Alphy.Level

It's likely one of the fields is null.
Some value + Null = Null.
Use the Nz() function. Look it up in VBA help.

Expr1:Nz([Level],0) - Nz([Upright],0) - Nz([Chest],0) -
Nz([Storeroom],0) - Nz([Kitchen],0)
Click to expand...
 
Top