By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Display contents of a field based on another field being yes

P: 93
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.
Feb 5 '09 #1
Share this Question
Share on Google+
11 Replies

P: 93
Sorry forgive me it is not a yes/no text however a check box.
Feb 5 '09 #2

Expert 100+
P: 1,287
If you are talking about a textbox on a form, you can use
Feb 5 '09 #3

P: 93
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.
Feb 5 '09 #4

Expert 100+
P: 1,287
What you need then, is to define a new field in your query as an expression with that same iif statement.
Feb 5 '09 #5

P: 93
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.
Feb 5 '09 #6

Expert 100+
P: 1,287
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.
Feb 5 '09 #7

P: 93
I am getting there here is what I have

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 5 '09 #8

Expert 100+
P: 1,287
[isChecked] refers to the checkbox field in your table, just replace that with the actual name of your field.
Feb 5 '09 #9

P: 93
Ugh! I knew that. You are a genious....It works perfectly now. Thanks so much.
Feb 5 '09 #10

P: 93
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?
Feb 5 '09 #11

P: 93
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.
Feb 5 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.