Nz function problem

[warwica]

I have a report based on a cross-tab query and on the report I want to use the Nz function for the summarised fileds from the cross-tab but I am having problems with it.

When I use Nz([FieldName],0) in a textbox the report displays errors instead of a number or zero as I want it to. On investigating, the field variable type is 'Error' and not 'Null' which explains why I get errors showing. Is this something to do with the report being based on a cross-tab query ?

Finally, if I set one textbox simply to 'Fieldname' and another to 'Nz([FieldName],0) the Nz text box does then display correctly and still does even if i then delete the text box with 'FieldName' in it.

 

[bradcccs]

Firstly, a possibility on your Finally:

Finally, if I set one textbox simply to 'Fieldname' and another to 'Nz([FieldName],0) the Nz text box does then display correctly and still does even if i then delete the text box with 'FieldName' in it.

Reason why this option works, and not your first option.

It sounds like you may have created your Report by a wizard (or made the same mistake - loose term)

The wizard creates your textboxes with the same name as your source field. ie: your box name will be FieldName, as will be your FieldNames name (gee, glad thats now clear). Thus, when you are looking for a nz() representation, access tends to look at the text boxes first. Thus your text box is attempting to refer to itself.

Change the text box names generated by your wizard to FieldNameBox or something similar. On first inspection, this may not appear to work, as Access also changes the entered formulas. You will have to correct these to FieldName only.

You should have a text box called: FieldNameBox
With the formula: nz(fieldname,0) etc.

I hope that hasn't completely confused you.

Brad.

 

[warwica]

Thanks Brad. It looks like text box names was the problem. I bet you can tell I'm a beginner.

 

[Fizzio]

Nice of microsoft to 'help us out' by naming the controls the same as the fieldnames when using the wizard eh?

Use this as a guide to help avoid this 'feature'

http://www.mvps.org/access/general/gen0012.htm