Nested IIF in Expression of Calculated Table Field (1 Viewer)
- Thread starter Jonny
- Start date
- Local time
- Today, 13:04
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. It looks like you're trying to create a calculated field. If so, you can't use VBA at the table level. You actually have a limited amount of functions available to you at this point. What exactly are you trying to calculate?
- Local time
- Today, 13:04
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. I believe IIf() statements can only have up to seven (7) layers (or was it 14?). If you have more than that, and it's probably a better approach anyway, it might be easier to create a separate table of these values and simply link the two tables together in a query.
- Local time
- Today, 13:04
- Joined
- Oct 29, 2018
- Messages
- 21,454
Again, though, VBA is not available at the table level, so you can't use it for Calculated fields. I mean, you could try with just one or two options in VBA and see if you can use it in the table. If you can, you can finish building it. If you can't, then you know you didn't waste your time building something you can't use.
- Local time
- Today, 16:04
- Joined
- Feb 19, 2002
- Messages
- 43,231
Rules on the column in the table is not the way to implement your requirement.
1. Make a separate table of the Roles with RoleID and RoleName
2. Add a RoleID field to the employee table. Set its default to null. If Role is required, make the field required.
3. In the relationship window of the BE database, create a relationship between the two tables and check the enforce RI box.
4. On your data entry form, add a combo that uses the Role table as a control source. If you let the wizard build the combo, it will hide the RoleID field and only display the text.
1. Make a separate table of the Roles with RoleID and RoleName
2. Add a RoleID field to the employee table. Set its default to null. If Role is required, make the field required.
3. In the relationship window of the BE database, create a relationship between the two tables and check the enforce RI box.
4. On your data entry form, add a combo that uses the Role table as a control source. If you let the wizard build the combo, it will hide the RoleID field and only display the text.
