Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.

My crosstab is something like this:

.........A...........B..............C............. D
1.......X

2.......X..........................X

3...................X............................. .X

(Ignore the dots, obviously)

Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.

I've tried using the Nz function but haven't been able to make it work. Thankyou in advance! :)

How are you using the nz()? Does it error or give you the exact same results?

I've tried:
Expr1: Nz([MyField],"No Booking")
As a field in my query, but that does nothing, i still get blank spaces.
Ideally, all columns need to be visible even if they contain no bookings, just so "No Booking" can be inserted into the fields, if that makes sense.

Thanks!

You dont need the nz() function.

Just change the format of the field to

0;0;0;"No Bookings"

For reference the format for Number/Currency is

Positive Number ; Negative Number ; Zero Value ; Null Values
You can also use colours

So:

0;0;0;[red]"No Bookings"

May be nice

You were correct - i didn't need Nz! I managed to figure this out earlier, by shoving this in my report fields:

=IIf(IsNull([A]),"No Booking",[A])

Appreciate your help :)

Follow up,

Using the method I gave is FAR less expensive than the way you are using.

IIf needs to evaluate both arguements before it yields a result.
Formating, doubt you'll ever see any difference, ever.

If your report is running fine, then no problem, but if you need to query a bigger dataset then look back at this reply.

Cheers,