Field Properties & Values

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I have built a table that I am populating from a .dbf
file. I have 3 columns that are giving me a problem -
debits, credits & balance. After populating the table, I
have selected records that have a null value - there is
nothing in the credit field. In every case, the null value
should be .00. So, I have tried using a custom format
(0;;;".00") and (0;;;0) for this field to replace nulls
with .00. When I view the table the .00 appears in the
null fields, but I can't use them in a calculation.
(Balance = debits-credits). For any field that was null
originally, I get a null balance. It's like the .00
entered through the field format is not really there.

any ideas how I can get past this issue or what is causing
my problem?
 
-----Original Message-----
I have built a table that I am populating from a .dbf
file. I have 3 columns that are giving me a problem -
debits, credits & balance. After populating the table, I
have selected records that have a null value - there is
nothing in the credit field. In every case, the null value
should be .00. So, I have tried using a custom format
(0;;;".00") and (0;;;0) for this field to replace nulls
with .00. When I view the table the .00 appears in the
null fields, but I can't use them in a calculation.
(Balance = debits-credits). For any field that was null
originally, I get a null balance. It's like the .00
entered through the field format is not really there.

any ideas how I can get past this issue or what is causing
my problem?
.
I should also add that I have tried using the IIf(Is Null
Click to expand...
([ap_credits]),0)but when I perform the calculation I get
null values in the balance due to type conversion failure.
 
Ted said:
I have built a table that I am populating from a .dbf
file. I have 3 columns that are giving me a problem -
debits, credits & balance. After populating the table, I
have selected records that have a null value - there is
nothing in the credit field. In every case, the null value
should be .00. So, I have tried using a custom format
(0;;;".00") and (0;;;0) for this field to replace nulls
with .00. When I view the table the .00 appears in the
null fields, but I can't use them in a calculation.
(Balance = debits-credits). For any field that was null
originally, I get a null balance. It's like the .00
entered through the field format is not really there.

any ideas how I can get past this issue or what is causing
my problem?
Click to expand...
Hi,
By putting a mask on you are only changing the way the field is displayed,
not the contents of the field. You need to do an update query. If you are
nervous about this, or it is the first time you're doing one make a copy of
your table, and practice on that first.

Make a new query with the table as the source.
In criteria under credits enter Is Null
run - all your null records should be returned.
on the toolbar should be a grid with a dropdown arrow - change the grid to
an update query.
You will now have a new line in the query builder.
Under credits in the new line enter the amount you want .00
Run
If you run a second time no records should be updated.
HTH
Marc
 
-----Original Message-----


Hi,
By putting a mask on you are only changing the way the field is displayed,
not the contents of the field. You need to do an update query. If you are
nervous about this, or it is the first time you're doing one make a copy of
your table, and practice on that first.

Make a new query with the table as the source.
In criteria under credits enter Is Null
run - all your null records should be returned.
on the toolbar should be a grid with a dropdown arrow - change the grid to
an update query.
You will now have a new line in the query builder.
Under credits in the new line enter the amount you want .00
Run
If you run a second time no records should be updated.
HTH
Marc


.
Marc - Thanks for the direction. You explained it well
Click to expand...
and the resolution worked like a charm.
 
Back
Top