Assigning value to blank data

[ChuckW]

Hi,

I run a query based on an invoice table that calculates
how many hours the staff members have worked. Every
week, the person who enters the invoices forgets to put
the staff persons name but their hours appears. So My
query has two columns: Staff and Hours. There is
usually about five or six records with the first being a
blank value where the data entry person forgot to enter
the staff. The second column totals the hours worked. I
want to build an access report and assign the term or
phrase "Not Classed" wheere their is a blank value. Is
there a way in my query or report to do this?

Thanks,

Chuck

 

[Rick B]

You can replace a null value by using the Nz function. It will either use
the value in the field, or replace it if it is null. Something like...


Nz([SomeField],"Not Classed")


Rick B

 

[ChuckW]

Rick,

Thanks for your help. I seem to be having problems
still. This is what my output looks like before using
the NZ function:

Therapist SumOfHours
2
Joe Jones 3
Jill Smith 5
Fred Flintstone 9
Betty Rubble 8

I placed your code in the criteria section of the
Therapist field. Here is what my SQL looks like:

SELECT WeeklyTherapistHours2.Therapist,
WeeklyTherapistHours2.SumOfHours
FROM WeeklyTherapistHours2
WHERE (((WeeklyTherapistHours2.Therapist)=Nz
([Therapist],"Not Classed")));

After I run the code, the null record with the summed
hours (i.e. 2) disapears. The others still appear.
Rather than having five records I now have four.

Any thoughts?

Thanks,

Chuck

-----Original Message-----
You can replace a null value by using the Nz function. It will either use
the value in the field, or replace it if it is null. Something like...


Nz([SomeField],"Not Classed")


Rick B

Hi,

I run a query based on an invoice table that calculates
how many hours the staff members have worked. Every
week, the person who enters the invoices forgets to put
the staff persons name but their hours appears. So My
query has two columns: Staff and Hours. There is
usually about five or six records with the first being a
blank value where the data entry person forgot to enter
the staff. The second column totals the hours worked. I
want to build an access report and assign the term or
phrase "Not Classed" wheere their is a blank value. Is
there a way in my query or report to do this?

Thanks,

Chuck

.

 

[Rick B]

I would have thought you wanted something like...

SELECT Nz(WeeklyTherapistHours2.Therapist,"Not Classed"),
WeeklyTherapistHours2.SumOfHours FROM WeeklyTherapistHours2;

Rick,

Thanks for your help. I seem to be having problems
still. This is what my output looks like before using
the NZ function:

Therapist SumOfHours
2
Joe Jones 3
Jill Smith 5
Fred Flintstone 9
Betty Rubble 8

I placed your code in the criteria section of the
Therapist field. Here is what my SQL looks like:

SELECT WeeklyTherapistHours2.Therapist,
WeeklyTherapistHours2.SumOfHours
FROM WeeklyTherapistHours2
WHERE (((WeeklyTherapistHours2.Therapist)=Nz
([Therapist],"Not Classed")));

After I run the code, the null record with the summed
hours (i.e. 2) disapears. The others still appear.
Rather than having five records I now have four.

Any thoughts?

Thanks,

Chuck

-----Original Message-----
You can replace a null value by using the Nz function. It will either use
the value in the field, or replace it if it is null. Something like...


Nz([SomeField],"Not Classed")


Rick B

Hi,

I run a query based on an invoice table that calculates
how many hours the staff members have worked. Every
week, the person who enters the invoices forgets to put
the staff persons name but their hours appears. So My
query has two columns: Staff and Hours. There is
usually about five or six records with the first being a
blank value where the data entry person forgot to enter
the staff. The second column totals the hours worked. I
want to build an access report and assign the term or
phrase "Not Classed" wheere their is a blank value. Is
there a way in my query or report to do this?

Thanks,

Chuck

.