Changing Fields

[radicalrik]

How would I change a field that has not data and insert data throughout the database?

For example... If I have a field call Maint cost and some of the fields have data (currency) in them, and some of them are blank. How would I change the blank fields into $0?

 

[Pat Hartman]

I would not do that. Having a price of $0 is different from having a null price. In the first case, someting costs $0 dollars and in the second, the price is unknown. If you are having trouble in some calculations, use the Nz() function whenever a field might be null.

If in fact the null values should be 0 because the real value is 0 then use an update query with selction criteria that selects rows with a null price.

 

[radicalrik]

I would not do that. Having a price of $0 is different from having a null price. In the first case, someting costs $0 dollars and in the second, the price is unknown. If you are having trouble in some calculations, use the Nz() function whenever a field might be null.

If in fact the null values should be 0 because the real value is 0 then use an update query with selction criteria that selects rows with a null price.

How would that be coded if I wanted to have a total of all the maintenance cost at the end of the report?

 

[Pat Hartman]

When you use aggregate or domain aggregate functions, nulls are ignored so they don't cause any problems. Therefore a RecordSource in a report of -
=Sum(MaintCost)
will work fine even if the MaintCost on some records is null.

However, if you need to do a calculation on the field, then you need to worry about nulls -

=Sum(MaintCost * 1.1)
would cause a problem so you need to use:
=Sum(Nz(MaintCost,0) * 1.1)

 

[HJAMES]

How would I do this in a query if I want to select only records that are greater than 0. I am now using >0 in the criteria. If I encounter a blank field I get "data type mismatch in criteria expression".

 

[jfgambit]

Change the criteria of the field to be IS NOT NULL...


HTH