IIF to Change Another Field - Microsoft Access Expert 25


	Back to Access Expert 25 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

IIF to Change Another Field

Kent Jamison

3 years ago

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

	Kevin Robertson @Reply 3 years ago
Update Queries

	Scott Axton @Reply 3 years ago
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 Jamison OP @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.

	Kevin Robertson @Reply 3 years ago
`LastEmployee: IIf([NewPosition],"N/A",[FormerEmployee])`

	Kent Jamison OP @Reply 3 years ago
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 Jamison OP @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.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 25.

Next Unseen

New Feature: Comment Live View