I'm posting this question in relation to Access Expert Course 25, Lesson 4. I've noticed in several courses you create an alias in a query when using IIF functions. I understand an alias is required to use IIF in a query, per MS Help feature. I also notice you're usually manipulating a single field, based on a condition of that same field. I get that part. But what if you want to use IIF (and a query alias) based on the condition of one field to change the input in _a different field_? For example, if field x is a Y/N type, and I click the yes box, can I force Access to enter "N/A" in _another_ field? Another example: Say I have two fields: 1) FormerEmployee and 2) NewPosition. If NewPosition is a Yes/No field, and I choose check the box (meaning Yes), can I force Access to enter "N/A" (meaning "not applicable") in the FormerEmployee field? I've tried doing this, and Access enters a zero (0) instead of "N/A" in the FormerEmployee field. Here's what I have written: LastEmployee: IIf([NewPosition]=Yes,[FormerEmployee]="N/A",[FormerEmployee]) Please advise. --Kent Jamison
Kent the way I'm reading your post is that both fields, FormerEmployee and NewPosition, are of the Y/N data type fields. Correct?
If so, you can't store text ( "N/A" ) in a Y/N field. You can only store -1, 0, Null. Even though you may be seeing Yes / No or True / False Access stores -1 for True or Yes and 0 for False or No and just displays them accordingly.
Kent JamisonOP
@Reply 3 years ago
@Scott Axton. No. FormerEmployee is a Short Text field where I put the last name of the employee who last held that position. NewPosition is a Y/N field. That's why I don't understand why the code above renders a "0" in FormerEmployee field. All other enteries are last names.
Thanks Kevin. That works as far as displaying "N/A" in the form as well as datasheet view for the query. However, when I need to add a name for the LastEmployee, that field is not editable. In other words, I cannot change the values in LastEmployee from the Form where I place the field LastEmployee. Anyway to tackle that?
Kevin Robertson
@Reply 3 years ago
You could overlay the two Text Boxes (FormerEmployee & LastEmployee) and control the visibility in the After Update event of the Check Box. When the box is checked show the calculated field displaying "N/A" and when the box is unchecked show the field from the table (FormerEmployee) which will be editable so you will be able to enter the name.
Kent JamisonOP
@Reply 3 years ago
@ Kevin Robertson. Just a follow up to what I ended up doing. I did it.... I ventured into a little VBA code. This eliminated the need to create and use the calculated LastEmployee field. Your tip to watch the After Update video gave me the idea: If I can make a field visible with VBA by clicking on a check box, why do I need two fields? Just turn one off when the check box is checked.
So, I wrote the following simple VBA command and placed it in After Update and On Current:
Private Sub Form_[Event Name]()
FormerEmployee.Visible = IIF(NewPosition, False, True)
End Sub
Now I just have the FormerEmployee field. If I click NewPosition check box, the FormerEmployee field disappears. N/A also not needed.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Expert 25.
