Return text "N/A" if field is empty 1

[nunan]

Hi

I am not very good when it comes to writing code but I assume this is a very simple question.

I have a report and in it's controlling query I want to define that if a field is empty then the report should show the text N/A in that field.

For example, the report may look like this if the phone number field in the table is empty:

Name: Joe Bloggs
DOB: 01/01/04
Phone No: N/A
Email: joe.bloggs@bloggs.com


Thanks for any help

 

[LNBruno]

If the field is truly empty, you could use the IIF function:

IIF([phone]="","N/A",[phone]

If the field contains null values, use the NZ function:

Nz([phone],"N/A")

< M!ke >

 

[dhookom]

=Nz([YourField],"n/a")
Make sure the name of the control is not also the name of the field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nunan]

Hi

Thanks for the replies, are you saying that this code should go in the query? Because it doesn't seem to work.

I want to be able to put something in the criteria field so that it is easily accessible, therefore I don't understand why I would need to specify a field name as the code will be in that field!!!

I hope I am making sense

Thanks

 

[dhookom]

Both solutions should be used as the control sources of text boxes on a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nunan]

Hi

Thanks.

I don't want to put code in the report as people will not have access to it, is there anything I can put in the criteria field of the query?

Thanks

 

[dhookom]

1) this is not code, it is an expression
2) why would you want people to have access to your designs?
3) where else would you put it that would afford better access?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nunan]

Apologies for calling an expression, code.

With all due respect, none of your questions are relevant, I just wanted to know if this was possible and if so what would I type. If it isn't possible then not to worry and thank you all for the quick and detailed help you have given.

Thanks

 

[BNPMike]

On these forums we often see people asking how to chop the arms of their overcoat whilst they are wearing it. They have a specific question about the size of axe to use. With a little probing about what they really want to achieve we often find they are merely too hot. We can then suggest they use the zip instead, thus preserving their limbs for typing further questions at a later date.

 

[LNBruno]

Very PC response which captured the essence of my initial reaction and worthy of one of those little pink five-pointed thingys! ;-)

< M!ke >

 

[BNPMike]

M!ke

It's little acts of generosity like that, that make it worthwhile coming on here

Mike