IIF Default Value - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

IIF Default Value

Jennifer Showalter

4 years ago

I want to use the party type [TypeOParty] to fill the BasicPartyPrice default value. I typed in the expression below but it gave me an error message (The database engine does not recognize ether the field 'TypeOParty' in a validation expression, or the default value in the table 'Party') TypeOParty is a combo box with 4 possible values. (Basic, Deluxe, Teen, Supreme)

IIF([TypeOParty]=1,125,IIF([TypeOParty]=2,195,IIF([TypeOParty]=3,200,215)))

Adam Schwanz @Reply

4 years ago

To confirm, TypeOParty has 2 columns, the ID field as the bound field and the string value as the second field right?

Is TypeOParty bound to anything or just an unbound selection field?

To me, this makes more sense to run in an after-update event of the combo box, setting the value based on the selection in the combo box, or in the on-current event of the form, but maybe I'm not understanding your setup. If I'm way off, include some images please Snip & Sketch

	Scott Axton @Reply 4 years ago
Just based on the error message I would suspect spelling to be a problem. You might want to double check.

	Jennifer Showalter OP @Reply 4 years ago
TypeOParty is a combo box based on a table but saves the result 1,2,3, or 4. I tried using =1 (no quotes) or ="Basic" in the function. It is not a spelling error, but for some reason it is not recognizing the field.

	Richard Rost @Reply 4 years ago
I'd recommend using the Switch function instead of Nested IIFs. Where are you putting this? And yes, let's see some screen shots, including the properties for everything involved.

	Scott Axton @Reply 4 years ago
Once in a while I've seen Access just loose it's mind. I've found that some times you are better off deleting the control / combo box. Save and close the Form. Compact and repair the database. Then close and restart it. Starting fresh often fixes my "weird" problems.

	Richard Rost @Reply 4 years ago
That's the truth, Scott. Don't forget to run down the Troubleshooter, Jennifer.

	Jennifer Showalter OP @Reply 4 years ago
I tested the switch function in a query and it worked fine. It just doesn't work in the Default Value line of the field. It is not really NECESSARY as they can type it in (and need to be able to edit it if it is discounted for summer) but it would be a little easier if it just filled based on the party type.

	Richard Rost @Reply 4 years ago
You generally can't put equations like that in the Default Value. You'd need a Before Insert VBA event.

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

Next Unseen

New Feature: Comment Live View