Default zero not showing up

[knowshowrosegrows]

I have a table that has four number fields. Those four fields are all set to
zero default. Using a query, I populate the table using a form. The
controls on the form do not have a default.

All of the fields but one show a zero when I open the form. That one field
throws an error message that reads "Field cannot be updated!" When I enter
test records, the table has zeros in the appropriate fields where I have
entered no data.

When I produce a report from a query that uses those fields, I do not get my
zeros.

I need to be able to update that field and I need my zeros in my report.

Your kind attention is, well, kind.

 

[Jerry Whittle]

Show us the SQL for the query. Open the query in design view. Next go to
View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.

Check out the form for any code on the field in question or the entire form
that could mess with the data.

Would the field in question happen to have a lookup to another field at
table level?

 

[Jerry Whittle]

The SQL looks fine and shouldn't be the problem.

Open the table in question and sort on the Census field. Sort both ascending
and decending order. Do you see any null values? The default value only is
applied to new records. Existing records with nulls will not show zeros.

If this is the problem, you could update the nulls to 0s with something like
the following:

UPDATE tblCensusEvent
SET tblCensusEvent.Census = 0
WHERE tblCensusEvent.Census Is Null;

If that doens't fix things, I'd worry about the "Field cannot be updated!"
error message. If you are using the query to update the tables, it could be
an unupdateable problem as you have joins between the various tables. Usually
it's best to have a main form and subforms to edit multiple tables.

Also there could be a corruption issue. Have you tried a compact and repair?

Lastly is that field part of a unique index? If so that might be blocking
things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks so much for your thorough and swift reply. I just finished reading
Building Databases for Mere Mortals. Soon I start SQL Queries for Mere
Mortals....

The field in question is Census from tblCensusEvent. It is not a PK or FK
and is not connected to any LookUp.

qryCensusEvent =
SELECT [Capacity]-[Census]+[Discharges]-[Admiss] AS [Slots Available],
tblCensusEvent.Census_ID, tblCensusEvent.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblCensusEvent.WaitList, tblCensusEvent.Staff_ID, tblProgram.Capacity,
tblProgram.Contact_ID, tblProgram.LOC_ID, tblProgram.Agency_ID,
tblContactInfo.CensusContactName, tblContactInfo.CensusContactPhone,
tblContactInfo.AdmissContactName, tblContactInfo.AdmissionsContactPhone
FROM (tblContactInfo INNER JOIN tblProgram ON tblContactInfo.Contact_ID =
tblProgram.Contact_ID) INNER JOIN tblCensusEvent ON tblProgram.Prm_Code =
tblCensusEvent.Prm_Code;

tblCensusEvent =
Census_ID PK
Prm_Code FK Many to One w/ tblProgram
Staff_ID FK

tblStaff
Staff_ID PK

tblProgram =
Prm_Code PK
Agency_ID FK
LOC_ID FK
Contact_ID FK

tblAgency =
Agency_ID PK

tblLOC =
LOC_ID PK

tblContactInfo =
Contact_ID PK


--
Thanks

Show us the SQL for the query. Open the query in design view. Next go to
View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.

Check out the form for any code on the field in question or the entire form
that could mess with the data.

Would the field in question happen to have a lookup to another field at
table level?

 

[knowshowrosegrows]

Thanks so much for your thorough and swift reply. I just finished reading
Building Databases for Mere Mortals. Soon I start SQL Queries for Mere
Mortals....

The field in question is Census from tblCensusEvent. It is not a PK or FK
and is not connected to any LookUp.

qryCensusEvent =
SELECT [Capacity]-[Census]+[Discharges]-[Admiss] AS [Slots Available],
tblCensusEvent.Census_ID, tblCensusEvent.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblCensusEvent.WaitList, tblCensusEvent.Staff_ID, tblProgram.Capacity,
tblProgram.Contact_ID, tblProgram.LOC_ID, tblProgram.Agency_ID,
tblContactInfo.CensusContactName, tblContactInfo.CensusContactPhone,
tblContactInfo.AdmissContactName, tblContactInfo.AdmissionsContactPhone
FROM (tblContactInfo INNER JOIN tblProgram ON tblContactInfo.Contact_ID =
tblProgram.Contact_ID) INNER JOIN tblCensusEvent ON tblProgram.Prm_Code =
tblCensusEvent.Prm_Code;

tblCensusEvent =
Census_ID PK
Prm_Code FK Many to One w/ tblProgram
Staff_ID FK

tblStaff
Staff_ID PK

tblProgram =
Prm_Code PK
Agency_ID FK
LOC_ID FK
Contact_ID FK

tblAgency =
Agency_ID PK

tblLOC =
LOC_ID PK

tblContactInfo =
Contact_ID PK

 

[knowshowrosegrows]

The table has no null values. There is a zero in all the places I need a
zero. It is when I make a report that the zeros disappear. This really
doesnt work when I publish the report to Microsoft Word because I lose my
formatting in the columns and numbers move left into places where there
should be zeros.

I cleaned up the query some and it made the "Field cannot be updated!"
message go away! Grazie Grazie Grazie Grazie!

Now I just need some more ideas about where my zeros have gone to.
--
Thanks

The SQL looks fine and shouldn't be the problem.

Open the table in question and sort on the Census field. Sort both ascending
and decending order. Do you see any null values? The default value only is
applied to new records. Existing records with nulls will not show zeros.

If this is the problem, you could update the nulls to 0s with something like
the following:

UPDATE tblCensusEvent
SET tblCensusEvent.Census = 0
WHERE tblCensusEvent.Census Is Null;

If that doens't fix things, I'd worry about the "Field cannot be updated!"
error message. If you are using the query to update the tables, it could be
an unupdateable problem as you have joins between the various tables. Usually
it's best to have a main form and subforms to edit multiple tables.

Also there could be a corruption issue. Have you tried a compact and repair?

Lastly is that field part of a unique index? If so that might be blocking
things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks so much for your thorough and swift reply. I just finished reading
Building Databases for Mere Mortals. Soon I start SQL Queries for Mere
Mortals....

The field in question is Census from tblCensusEvent. It is not a PK or FK
and is not connected to any LookUp.

qryCensusEvent =
SELECT [Capacity]-[Census]+[Discharges]-[Admiss] AS [Slots Available],
tblCensusEvent.Census_ID, tblCensusEvent.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblCensusEvent.WaitList, tblCensusEvent.Staff_ID, tblProgram.Capacity,
tblProgram.Contact_ID, tblProgram.LOC_ID, tblProgram.Agency_ID,
tblContactInfo.CensusContactName, tblContactInfo.CensusContactPhone,
tblContactInfo.AdmissContactName, tblContactInfo.AdmissionsContactPhone
FROM (tblContactInfo INNER JOIN tblProgram ON tblContactInfo.Contact_ID =
tblProgram.Contact_ID) INNER JOIN tblCensusEvent ON tblProgram.Prm_Code =
tblCensusEvent.Prm_Code;

tblCensusEvent =
Census_ID PK
Prm_Code FK Many to One w/ tblProgram
Staff_ID FK

tblStaff
Staff_ID PK

tblProgram =
Prm_Code PK
Agency_ID FK
LOC_ID FK
Contact_ID FK

tblAgency =
Agency_ID PK

tblLOC =
LOC_ID PK

tblContactInfo =
Contact_ID PK


--
Thanks

Show us the SQL for the query. Open the query in design view. Next go to
View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.

Check out the form for any code on the field in question or the entire form
that could mess with the data.

Would the field in question happen to have a lookup to another field at
table level?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a table that has four number fields. Those four fields are all set to
zero default. Using a query, I populate the table using a form. The
controls on the form do not have a default.

All of the fields but one show a zero when I open the form. That one field
throws an error message that reads "Field cannot be updated!" When I enter
test records, the table has zeros in the appropriate fields where I have
entered no data.

When I produce a report from a query that uses those fields, I do not get my
zeros.

I need to be able to update that field and I need my zeros in my report.

Your kind attention is, well, kind.