Calculated firld in a table

[angie]

I have a calculated field in a table named FinancedPrice I have the following in the expression Round([price],0)

I have a field MiscPrice that I would like to add the total to the calculated field financedprice.



Sometime this miscprice field is null.

Thank you Angie

[pbaldy]

You can use the Nz() function I think.

[kd2017]

An alternative to pbaldy's suggestion is to use the IIF() function. TotalPrice: IIF([MiscPrice] is null, 0, [MiscPrice]*[FinancedPrice])
The advantages here being your results will be of the correct data type.
http://allenbrowne.com/QueryPerfIssue.html

[angie]

An alternative to pbaldy's suggestion is to use the IIF() function. TotalPrice: IIF([MiscPrice] is null, 0, [MiscPrice]*[FinancedPrice])
The advantages here being your results will be of the correct data type.
http://allenbrowne.com/QueryPerfIssue.html

I am trying to add the two together in a calculated table field.

[pbaldy]

I believe the Nz() will return the correct type, as long as you specify the replacement value. I'm not sure if it's allowed in calculated fields though.

[kd2017]

I believe the Nz() will return the correct type, as long as you specify the replacement value. I'm not sure if it's allowed in calculated fields though.

As I understand it vba's Nz() returns a variant which jet doesn't understand so jet turns them into strings in a query. I've just confirmed this with a quick test query in access 2016, both nz parameters were numerics but the query returned strings.

The Nz() function replaces Null with another value (usually a zero for numbers, or a zero-length string for text.) The new value is a Variant data type, and VBA tags it with a subtype: String, Long, Double, Date, or whatever.
This is great in VBA: a function can return different subtypes at different times. But in a query, a column can be only be ONE data type. JET therefore treats Variants as Text, since anything (numbers, dates, characters, ...) is valid in a Text column.
The visual clue that JET is treating the column as Text is the way it left-aligns. Numbers and dates display right-aligned.

If you expected a numeric or date column, you now have serious problems. Text fields are evaluated character-by-character. So 2 is greater than 19, because the first character (the 2) is greater than the first character of the other text (the 1 in 19.) Similarly, 4/1/2009 comes after 1/1/2010 in a Text column, because 4 comes after 1.

Alarm bells should ring as soon as you see a column left-aligned as Text, when you expected it handled numerically. Wrong records will be selected, and the sorting will be nonsense.
You could use typecast the expression with another VBA function call, but a better solution would be to let JET do the work instead of calling VBA at all.

[pbaldy]

In a query I agree. I'm not sure that applies in a calculated field. I don't use them, so I'll bow out.

[kd2017]

I don't believe Nz() is an available function within a calculated field expression.

[angie]

I don't believe Nz() is an available function within a calculated field expression.

I need to calculate a field in a form that will hold the information in the table. If I do this using a query will it show in the table in the field. Are should I use a Vb code in the field on the form ( on change) to add the two fields together.

[kd2017]

I have a calculated field in a table named FinancedPrice I have the following in the expression Round([price],0)

I have a field MiscPrice that I would like to add the total to the calculated field financedprice.

Sometime this miscprice field is null.

Thank you Angie

As pbaldy alluded to most prefer not to use calculated fields to store calculations within a table itself, it can lead to problems. Here is a short article you can read on the subject http://www.allenbrowne.com/casu-14.html

What most would do is instead of storing the results of a calculation in a table, they would instead store all the variables required to make the calculation, then they would do the arithmetic in a query (or form, or report) at runtime. If you have stored the results of a calculation in a table and the variables used to get calculation change the will not automatically be recalculated and now your calculation is wrong. But if you redo the calculation each time in a query you know the figure is reliable.

These videos will show you how to do calculations in a query:
https://www.youtube.com/watch?v=XeGUubpAcE0
https://www.youtube.com/watch?v=pAnfjCu0c-Q
https://www.youtube.com/watch?v=TKHyv1GhdDY

I am trying to add the two together in a calculated table field.

If you must use a calculated field directly within a table structure then you'll have to use the IIF() statement to check that your miscprice field is not null. IIF([miscprice] is null, 0, [miscprice]) will return zero if the miscprice is null or return miscprice if it is not null. To use it in the calculated field it might look like Round([price] + IIF([miscprice] is null, 0, [miscprice]), 0)

I need to calculate a field in a form that will hold the information in the table. If I do this using a query will it show in the table in the field. Are should I use a Vb code in the field on the form ( on change) to add the two fields together.

I think there should be no coding required for what you've described. You can make a query that performs your calculations and build your forms off that query just like you would build a form off a table. You can also do calculations directly in a textbox's control source.

[angie]

As pbaldy alluded to most prefer not to use calculated fields to store calculations within a table itself, it can lead to problems. Here is a short article you can read on the subject http://www.allenbrowne.com/casu-14.html

What most would do is instead of storing the results of a calculation in a table, they would instead store all the variables required to make the calculation, then they would do the arithmetic in a query (or form, or report) at runtime. If you have stored the results of a calculation in a table and the variables used to get calculation change the will not automatically be recalculated and now your calculation is wrong. But if you redo the calculation each time in a query you know the figure is reliable.

These videos will show you how to do calculations in a query:
https://www.youtube.com/watch?v=XeGUubpAcE0
https://www.youtube.com/watch?v=pAnfjCu0c-Q
https://www.youtube.com/watch?v=TKHyv1GhdDY



If you must use a calculated field directly within a table structure then you'll have to use the IIF() statement to check that your miscprice field is not null. IIF([miscprice] is null, 0, [miscprice]) will return zero if the miscprice is null or return miscprice if it is not null. To use it in the calculated field it might look like Round([price] + IIF([miscprice] is null, 0, [miscprice]), 0)



I think there should be no coding required for what you've described. You can make a query that performs your calculations and build your forms off that query just like you would build a form off a table. You can also do calculations directly in a textbox's control source.

Thank you for the information. I went back to using a query.