Blank result, want a zero

[Ann]

I have three fields. They are [initials], [type of time] and [amount]. I
need the sum of the [amount] field for [type of time] that is either "comp
time earned" or "comp time used" so I created a crosstab query. [Initials]
is the row heading, [type of time] is the column heading and [amount] is the
value. When I run the query there are four records. Two that have both
"comp time earned" and "comp time used" are fine. They have values in both.
The two that have only "comp time earned" records have values in that column
but the "comp time used" column value is blank. At this point they should be
since they don't have any "comp time used" records in the table. I want them
to be zero so I can do other calculations so I tried to use Nz but that
didn't work. I also found information on using Val(Nz) in the sql but that
didn't work either. Can anyone help me? Thanks

 

[Michel Walsh]

Edit your crosstab query in SQL view. From something like:

TRANSFORM SUM(someField)
SELECT ...


change it to

TRANSFORM Nz(SUM(someField), 0 )
SELECT ...



Hoping it may help,
Vanderghast, Access MVP

 

[Ann]

That worked...you're amazing!!!

I appreciate your help.