Display contents of a field based on another field being yes

[clloyd]

I have a phone number field (BusinessPhone) that I only want to display in my query results if the user has selected yes (yes/no field) in another field (Business Y/N field).

The premise is that they can choose to use their business phone, mobile phone or both. If they choose yes in business their business phone would appear in the field BusinessPhone however if they choose no the query should return nothing in that field for that user.

Thanks in advance.

[clloyd]

Sorry forgive me it is not a yes/no text however a check box.

[ChipR]

If you are talking about a textbox on a form, you can use
=IIf(isChecked, BussinessPhone, "")

[clloyd]

It is a query. I am running a query to export to Excel. I need something in the criteria of that field to only display the contents for that user if Business is checked.

[ChipR]

What you need then, is to define a new field in your query as an expression with that same iif statement.

[clloyd]

Not an expert programmer. I am not having any luck defining the new field(s). Keep in mind I have to set criteria on two fields one may display information and the other not if one is checked (BusinessPhone) and the other (MobilePhone) is not checked.

[ChipR]

If you are creating your query in design view, you can just type that expression in the next open Field box. Acess will stick "Expr1:" in front of it, and you can change that to whatever you want the field to be called in the result of the query.

[clloyd]

I am getting there here is what I have

BusinessPhonePreferred: IIf([isChecked],[BusinessPhone],"")

When I run it it comes up isChecked

If I type in Business it gives me a result. If I type nothing the field is left blank. If I uncheck the field and run it and type in Business at isChecked it gives me the phone number when I don't want it.

I am missing something.

Thanks for all your help

[ChipR]

[isChecked] refers to the checkbox field in your table, just replace that with the actual name of your field.

[clloyd]

Ugh! I knew that. You are a genious....It works perfectly now. Thanks so much.

[clloyd]

One more problem. When I write the same same code for the mobile phone I get a "circular reference" error. I really need them to work together. Any suggestions?

[clloyd]

Never mind I found it. I was trying to name the field the same as the actual field. I am having one of those days. All works good.