If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Default zero not showing up
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. -- Thanks |
#2
|
|||
|
|||
Default zero not showing up
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. "knowshowrosegrows" wrote: 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. -- Thanks |
#3
|
|||
|
|||
Default zero not showing up
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. "knowshowrosegrows" wrote: 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 "Jerry Whittle" wrote: 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. "knowshowrosegrows" wrote: 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. -- Thanks |
#4
|
|||
|
|||
Default zero not showing up
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 "Jerry Whittle" wrote: 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. "knowshowrosegrows" wrote: 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. -- Thanks |
#5
|
|||
|
|||
Default zero not showing up
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 "Jerry Whittle" wrote: 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. "knowshowrosegrows" wrote: 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 "Jerry Whittle" wrote: 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. "knowshowrosegrows" wrote: 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. -- Thanks |
Thread Tools | |
Display Modes | |
|
|