I know this has been covered but I'm not getting it. I have a select query of three tables. On one numeric field I want the nulls to be zero. This field name is present in all three tables. I want the query to return values (and zeros for nulls) from this field only from the third table. Therefore, I believe the syntax should include the table name, "tblDevice" and the field name, "PCO_Machine_Wafer_Processor" but I'm not sure how. I am also unsure if the statement should go in the Field box or the Criteria. Ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Null values convert to zero
- Thread starter shelspa
- Start date
I think that you should break your problem into three smaller problems: 1) the Null-zero issue, 2) the relationships between tables and 3) the field box vs query thing.
Here are my comments on each of the three issues-
(1) There are many ways to fix the null issue. I like to screen input to keep it clean and/or use macros to remove nulls. So my data usually never contains nulls. Another alternative is to screen out nulls in the queries.
(2) Tell us more about the tables and the relationships. Do you have experience developing relational databases?
(3) You want to use the contents of a text box as a criteria in a query. I would use this syntax in the criteria row for that field:
Forms![Form1]![textField1]
The form will need to be open for this to work.
Here are my comments on each of the three issues-
(1) There are many ways to fix the null issue. I like to screen input to keep it clean and/or use macros to remove nulls. So my data usually never contains nulls. Another alternative is to screen out nulls in the queries.
(2) Tell us more about the tables and the relationships. Do you have experience developing relational databases?
(3) You want to use the contents of a text box as a criteria in a query. I would use this syntax in the criteria row for that field:
Forms![Form1]![textField1]
The form will need to be open for this to work.
- Thread starter
- #4
Records are not included in my query when this field is null. Therefore I really need to assign them to 0 so I don't lose information. The tables are linked by a lot number field, first to second and second to third. The first and third do not contain the same lot number. I'm not sure about point #3.
On the first issue, perhaps you could clean the data. This query will change a null in a numeric field 0~
UPDATE Table1 SET Table1.q = 0
WHERE (((Table1.q) Is Null));
You can write several queries like this. Then you can put them in a macro. The "set warnings- off" action will suppress some messages. You might put that at the top of the macro. At the end of the macro set warnings- on.
In the future, try to stop users from entering nulls. You can do this in your forms.
On the second issue, perhaps you could tell us the structure of the tables. List the fields, data types, primary keys, and foreign keys.
I have already mentioned how to put values from a form into a query's parameters.
UPDATE Table1 SET Table1.q = 0
WHERE (((Table1.q) Is Null));
You can write several queries like this. Then you can put them in a macro. The "set warnings- off" action will suppress some messages. You might put that at the top of the macro. At the end of the macro set warnings- on.
In the future, try to stop users from entering nulls. You can do this in your forms.
On the second issue, perhaps you could tell us the structure of the tables. List the fields, data types, primary keys, and foreign keys.
I have already mentioned how to put values from a form into a query's parameters.
- Status
Similar threads
- Question
- Locked
- Question