Chaning Blank Query Results to different value.

[Guest]

I import data from one source(called the Financial Cube) into an Access Table
TblFinancialCube. The financial cube uses an employee code, instead of
employee name, so I link TblFinancialCube with TblEmployee, which contains
the actual name of the employee and the financial cube code.

When I run the query, I get all the records that I want. But, when an
employee is no longer with the company the query returns the field [Employee
Last Name] as blank. When I generate a report on this query, I'd like to
change the empty field to "Unknown" so that I can better group the blank
records.

Any ideas?

 

[Rick B]

Use the Nz function to replace the null value with "Unknown"...


= Nz([Employee Last Name],"Unknown")




Rick B

 

[Guest]

Thank you. I presume I was supposed to enter the expression in the criteria
of the Employee Last Name field in my query.
I did that and the query returned only the results which had an actual
employee name. The records which were blank, no longer displayed.
Did I miss something? Any ideas why that might happen?

Use the Nz function to replace the null value with "Unknown"...


= Nz([Employee Last Name],"Unknown")




Rick B

I import data from one source(called the Financial Cube) into an Access Table
TblFinancialCube. The financial cube uses an employee code, instead of
employee name, so I link TblFinancialCube with TblEmployee, which contains
the actual name of the employee and the financial cube code.

When I run the query, I get all the records that I want. But, when an
employee is no longer with the company the query returns the field [Employee
Last Name] as blank. When I generate a report on this query, I'd like to
change the empty field to "Unknown" so that I can better group the blank
records.

Any ideas?

 

[Rick B]

No, don't enter that as a criteria. Criteria limits the returned results.

You wnat to use this in your report or form, not the query. The query
returned the records, now you need to decide how to display them in the
report or form.

Instead of pulling the [Employee Last Name] field to your report or form,
add an unbound text field. In that fiel, put the following..

= Nz([Employee Last Name],"Unknown")


This will cause the field to either display the Last Name, or "Unknown".

Rick B

Thank you. I presume I was supposed to enter the expression in the criteria
of the Employee Last Name field in my query.
I did that and the query returned only the results which had an actual
employee name. The records which were blank, no longer displayed.
Did I miss something? Any ideas why that might happen?

Use the Nz function to replace the null value with "Unknown"...


= Nz([Employee Last Name],"Unknown")




Rick B

I import data from one source(called the Financial Cube) into an

Access
Table

TblFinancialCube. The financial cube uses an employee code, instead of
employee name, so I link TblFinancialCube with TblEmployee, which contains
the actual name of the employee and the financial cube code.

When I run the query, I get all the records that I want. But, when an
employee is no longer with the company the query returns the field [Employee
Last Name] as blank. When I generate a report on this query, I'd like to
change the empty field to "Unknown" so that I can better group the blank
records.

Any ideas?

 

[Guest]

As they say in the Guiness Beer commercial, "Brilliant!".
Thank you for your help. Saved me a lot of time.

No, don't enter that as a criteria. Criteria limits the returned results.

You wnat to use this in your report or form, not the query. The query
returned the records, now you need to decide how to display them in the
report or form.

Instead of pulling the [Employee Last Name] field to your report or form,
add an unbound text field. In that fiel, put the following..

= Nz([Employee Last Name],"Unknown")


This will cause the field to either display the Last Name, or "Unknown".

Rick B

Thank you. I presume I was supposed to enter the expression in the criteria
of the Employee Last Name field in my query.
I did that and the query returned only the results which had an actual
employee name. The records which were blank, no longer displayed.
Did I miss something? Any ideas why that might happen?

Use the Nz function to replace the null value with "Unknown"...


= Nz([Employee Last Name],"Unknown")




Rick B


I import data from one source(called the Financial Cube) into an Access
Table
TblFinancialCube. The financial cube uses an employee code, instead of
employee name, so I link TblFinancialCube with TblEmployee, which contains
the actual name of the employee and the financial cube code.

When I run the query, I get all the records that I want. But, when an
employee is no longer with the company the query returns the field
[Employee
Last Name] as blank. When I generate a report on this query, I'd like to
change the empty field to "Unknown" so that I can better group the blank
records.

Any ideas?