Missing Fields in Report

[MDavidCrompton]

Hi,
New member in the last few moments.
The M stands for Michael which is my first name but I have always been called David.
UK resident, freelance developer and trainer in Access and Excel. Reasonably advanced in some respects but always being tripped up by simple things I have forgotten or never knew.
So here is my first:

I have a report in Access which depends in part for its data on a cross-tab query - it is actually summarising furniture floor by floor in a building. I set it up for a 3 floor building but have now fed in data for a 2 floor building and it crashes looking for the 3rd floor field from the cross-tab.

It must be easy for crying out loud. How do set a control to read from a field if it is there and stay blank if it isn't?????

Any assistance welcome

David

 

[Severin]

I'm not quite sure I understand but maybe you need to look at fixed column headings in the cross-tab query?~)

 

[MDavidCrompton]

Hi, Thanks but I really don't want to mess with the crosstab.

I just want a way of setting a control on a report to something like:

iif [Floor3] exists [Floor3] Else ""

Cheers

David

 

[Severin]

have you tried nz([Floor3]) or iif(isnull([Floor3]),"",[Floor3])

 

[MDavidCrompton]

Hi,
Tried isnull but that refers to current record. Never heard of "nz". I'm off to try it right now

 

[MDavidCrompton]

Nope, that also refers to the value of an existing field but I can think of a few other uses for it, thanks.

Is there a way of counting the number of available fields in a report or the number of columns in a query.

The crosstab will deliver one extra column per extra floor which in turn makes one more field for the report. If I could count them I could use conditional entries