Currency problems

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

In Access 2000, I have tables with fields as currency type. The queries
that use them show $0.00 for null values. The form has six currency fields,
and when actual values exist for these columns, they show in the text
controls, but when values are null, form controls remain blank. (shouldn't
they show $0.00?) I've set the format for the text control to currency and
the decimal places to 'auto.'

Subs that use those controls (add and multiply) do not seem to work either.

Nick
 
Why does the query show zeros for nulls?
Are these calculated fields, involving Nz()?
Or are you just referring to the new record row, where the Default Value
causes zeros to appear?

Access should not display zeros where there are nulls unless you
specifically instruct it to. For totals, use a combination of IIf() and Nz()
to force Access to use a zero value for null.
 
These are currency type fields. All currency and numeric data types have
the default value of 0, set in the table. The currency values in the table
are all $0.00 and always show this by default.

Nick
 
If your query is joining other tables and does not match an entry in both
tables, it will create nulls. These nulls will not show as a 0 since a
query is not actually creating data, just calling it up. Create another
query based on the first one and use the NZ function to turn these nulls
into 0s.

Kelvin
 
This is really confusing to me. (Naturally, I'm ignorant) The tables show
$0.00, but the queries show this $0.00 only for older records. New records
get blanks. I think I understand how to use Nz in vb code, but how does one
apply it to a query?

Me!ControlName = Nz(([UnitCharge} * [UnitsBilled]), 0)

If this is right.

Nick
 
Back
Top