Filling empty field values in a crosstab query !!!!! 1

[120282]

I need to fill the field values that have been returned empty on a crosstab query.

Or if there is a way to add fields values including empty ones.eg

mon tues wed thur fri total
3 4 1 1 3 12
2 2 1 2
1 4 2 5

 

[GingerR]

do you mean instead of blank you want them to be zero?
in your query you can try either
nz(FieldName,0) and if that doesn't work,
iif(FieldName = null,0,FieldName)

 

[120282]

if i enter either of these in the expression builder it returns a syntax errors.
Where should i be entering them?

Thanx
120282

 

[scriverb]

Create a Select query using your crosstab query as the input source. Now use the NZ(FieldName,0) on each of your column values including the total column. This will fill each of your empty spots with a zero. The totals are not rolling up for you because there is a null value as one of the column values for that row.

Post back with more questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

thread702-610860

MichaelRed
m.red@att.net

Searching for employment in all the wrong places