Inserting info. into null fields

[G.]

I think I will be a newbie forever! My problem - I need to create a report
based on data in 4 tables. I created a query to join the data I need from
each table. The query is directional - I used all records from one table and
only records that matched from another - because I need to have all people in
the database available in a report. In the query, some records are null but I
need these null values to say either 0 or N/A.

For example, 1 table has LIC#, lastName, FirstName. Another table has Lic#,
Obs, Exp. When joining the 2 tables I get all the Lic#'s, First_Name, and
Last_Name fields to list but if there is a Lic# on the first table that is
not on the second, the Obs and Exp fields will be null.

I hope I explained that OK. Thanks for any help.

 

[John Spencer]

Well, you can handle that in the query or you can handle it on the report.

One method is to use the NZ function which turns nulls into some specified
value. In a query use calculated fields in place of the fields that you want
to show something different than null.

Field: Obs: Nz([TableName].[Obs],0)

Field: Exp: NZ({tableName].[EXP],"N/A")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[G.]

Thanks John. I used the following in the field portion of the query:

name of new field: NZ([name of original field],0).

This seemed to work but it turned the values that were not null into
different numbers. Specifically, more #'s after the decimal. If the value was
7.54 before, now it is 7.548554. Also, I did the NZ function for a field that
had a % in it. Now, ratehr than saying 16.7%, as it did before, it says
0.166667. Any idea of what is going on or how I can fix it? Thanks.

Well, you can handle that in the query or you can handle it on the report.

One method is to use the NZ function which turns nulls into some specified
value. In a query use calculated fields in place of the fields that you want
to show something different than null.

Field: Obs: Nz([TableName].[Obs],0)

Field: Exp: NZ({tableName].[EXP],"N/A")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I think I will be a newbie forever! My problem - I need to create a report
based on data in 4 tables. I created a query to join the data I need from
each table. The query is directional - I used all records from one table and
only records that matched from another - because I need to have all people in
the database available in a report. In the query, some records are null but I
need these null values to say either 0 or N/A.

For example, 1 table has LIC#, lastName, FirstName. Another table has Lic#,
Obs, Exp. When joining the 2 tables I get all the Lic#'s, First_Name, and
Last_Name fields to list but if there is a Lic# on the first table that is
not on the second, the Obs and Exp fields will be null.

I hope I explained that OK. Thanks for any help.

 

[John Spencer]

It looks as if it is stripping off the formatting on the field. The numbers
you are seeing are probably waht is really being stored in the field.

You can use formatting to display the numbers, but they actual value should
not change. 0.166667 is 16.7% (approximately) when you have chosen to display
the value as percent format with 1 decimal place.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks John. I used the following in the field portion of the query:

name of new field: NZ([name of original field],0).

This seemed to work but it turned the values that were not null into
different numbers. Specifically, more #'s after the decimal. If the value was
7.54 before, now it is 7.548554. Also, I did the NZ function for a field that
had a % in it. Now, ratehr than saying 16.7%, as it did before, it says
0.166667. Any idea of what is going on or how I can fix it? Thanks.

Well, you can handle that in the query or you can handle it on the report.

One method is to use the NZ function which turns nulls into some specified
value. In a query use calculated fields in place of the fields that you want
to show something different than null.

Field: Obs: Nz([TableName].[Obs],0)

Field: Exp: NZ({tableName].[EXP],"N/A")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I think I will be a newbie forever! My problem - I need to create a report
based on data in 4 tables. I created a query to join the data I need from
each table. The query is directional - I used all records from one table and
only records that matched from another - because I need to have all people in
the database available in a report. In the query, some records are null but I
need these null values to say either 0 or N/A.

For example, 1 table has LIC#, lastName, FirstName. Another table has Lic#,
Obs, Exp. When joining the 2 tables I get all the Lic#'s, First_Name, and
Last_Name fields to list but if there is a Lic# on the first table that is
not on the second, the Obs and Exp fields will be null.

I hope I explained that OK. Thanks for any help.

 

[G.]

I am not sure where I should change the formatting. The error is showing up
in a query. I went to the table that the error field is based on and changed
the formatting to Data Type = Number, Field Size = Double, Format = Percent,
Decimal Places = 1. This had no effect on the query. Is there a place for me
to change the format in the query itself?

It looks as if it is stripping off the formatting on the field. The numbers
you are seeing are probably waht is really being stored in the field.

You can use formatting to display the numbers, but they actual value should
not change. 0.166667 is 16.7% (approximately) when you have chosen to display
the value as percent format with 1 decimal place.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks John. I used the following in the field portion of the query:

name of new field: NZ([name of original field],0).

This seemed to work but it turned the values that were not null into
different numbers. Specifically, more #'s after the decimal. If the value was
7.54 before, now it is 7.548554. Also, I did the NZ function for a field that
had a % in it. Now, ratehr than saying 16.7%, as it did before, it says
0.166667. Any idea of what is going on or how I can fix it? Thanks.

Well, you can handle that in the query or you can handle it on the report.

One method is to use the NZ function which turns nulls into some specified
value. In a query use calculated fields in place of the fields that you want
to show something different than null.

Field: Obs: Nz([TableName].[Obs],0)

Field: Exp: NZ({tableName].[EXP],"N/A")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. wrote:
I think I will be a newbie forever! My problem - I need to create a report
based on data in 4 tables. I created a query to join the data I need from
each table. The query is directional - I used all records from one table and
only records that matched from another - because I need to have all people in
the database available in a report. In the query, some records are null but I
need these null values to say either 0 or N/A.

For example, 1 table has LIC#, lastName, FirstName. Another table has Lic#,
Obs, Exp. When joining the 2 tables I get all the Lic#'s, First_Name, and
Last_Name fields to list but if there is a Lic# on the first table that is
not on the second, the Obs and Exp fields will be null.

I hope I explained that OK. Thanks for any help.

 

[G.]

OK after much bumbling I think I see what you are trying to say. If not, this
seems to do the trick too:

To display a 0 in a null field when building a query, with formatting of 2
decimal points:
name of new field: Format (NZ([name of original field],0) "0.00")

OR To display a N/A in a null field when building a query, with formatting
of percentage with 2 decimal points:

name of new field: Format (NZ([name of original field],"N/A") "0.00%")

I am not sure where I should change the formatting. The error is showing up
in a query. I went to the table that the error field is based on and changed
the formatting to Data Type = Number, Field Size = Double, Format = Percent,
Decimal Places = 1. This had no effect on the query. Is there a place for me
to change the format in the query itself?

It looks as if it is stripping off the formatting on the field. The numbers
you are seeing are probably waht is really being stored in the field.

You can use formatting to display the numbers, but they actual value should
not change. 0.166667 is 16.7% (approximately) when you have chosen to display
the value as percent format with 1 decimal place.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks John. I used the following in the field portion of the query:

name of new field: NZ([name of original field],0).

This seemed to work but it turned the values that were not null into
different numbers. Specifically, more #'s after the decimal. If the value was
7.54 before, now it is 7.548554. Also, I did the NZ function for a field that
had a % in it. Now, ratehr than saying 16.7%, as it did before, it says
0.166667. Any idea of what is going on or how I can fix it? Thanks.

:

Well, you can handle that in the query or you can handle it on the report.

One method is to use the NZ function which turns nulls into some specified
value. In a query use calculated fields in place of the fields that you want
to show something different than null.

Field: Obs: Nz([TableName].[Obs],0)

Field: Exp: NZ({tableName].[EXP],"N/A")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. wrote:
I think I will be a newbie forever! My problem - I need to create a report
based on data in 4 tables. I created a query to join the data I need from
each table. The query is directional - I used all records from one table and
only records that matched from another - because I need to have all people in
the database available in a report. In the query, some records are null but I
need these null values to say either 0 or N/A.

For example, 1 table has LIC#, lastName, FirstName. Another table has Lic#,
Obs, Exp. When joining the 2 tables I get all the Lic#'s, First_Name, and
Last_Name fields to list but if there is a Lic# on the first table that is
not on the second, the Obs and Exp fields will be null.

I hope I explained that OK. Thanks for any help.