Iif statement to add value in next column (1 Viewer)

Elmobram22 · Nov 12, 2013

Hi,

I am trying to add a value to a column in a query based on the value of another column. I am using an Iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column. I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes". Any help would be greatly appreciated.

Cheers,

Elmobram22

Mihail · Nov 12, 2013

And if not ????

pr2-eugin · Nov 12, 2013

So what are the Other cases? What if [Was Worker Born in UK] is 'Unknown' or 'No'?

Anyway, the general Syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition is the value that you want to test.
value_if_true is the value that is returned if condition evaluates to TRUE.
value_if_false is the value that is return if condition evaluates to FALSE.

Code:

[COUNTRYOFBIRTH]: IIF([Was Worker Born in UK] = "Yes", 866, 0)

Elmobram22 · Nov 12, 2013

Its part of a form where if you choose no you then choose from the next column drop down. The country of birth column is a drop down to choose the country. If worker is born in uk and the value is yes i want that column to point to 826 or whatever. So if you choose no before you will have already inputted the number. Am i better adding a brand new column and pulling the data to it from both sides?

pr2-eugin · Nov 12, 2013

Is this on a Form/Query? If it is a Form, you need to look into the concept of Cascading ComboBoxes.. If Query then you can use the CODE I posted earlier..

Mihail · Nov 12, 2013

Only for my understanding:
Why you need an extra field IsWorkerBornInUK ?!?!?
If in the field CountryOfBurn yoy select UK then the worker is born in UK. If not, not.

Brianwarnock · Nov 12, 2013

Mihail said:
Only for my understanding:
Why you need an extra field IsWorkerBornInUK ?!?!?
If in the field CountryOfBurn yoy select UK then the worker is born in UK. If not, not.

LOL

Amazing how often the obvious is overlooked.

Brian

Elmobram22 · Nov 12, 2013

I think the cascading combos is the way to go thanks for the info! I know what you mean guys about the obvious but the info I am putting in needs to match a database I am working on which has the options in. I don't want to destroy it for the basis of ease. Better to do it right from my end.

pr2-eugin · Nov 12, 2013

If it is a ComboBox on a Form, it would make more sense.. I would not class that as quiet obvious.. Good Luck !

Brianwarnock · Nov 12, 2013

Come on now Paul, from a user point of view it must be better to select all nationalities from one question, not have two questions ie is he UK , no , oh! What is he then? If UK is the expected answer then make that the default and then the user can just move on, heck you must have seen that often enough.
If the op is saying that he has to live with poor design fair enough it won't be the first or last that that happens.

Brian

Mihail · Nov 12, 2013

I know what you mean guys about the obvious but the info I am putting in needs to match a database I am working on which has the options in

If you already have a good DB why you wish to change it ?
And... forgive me for what I'll say (but I can't abstain):

At this time you should decide the structure for your TABLES.

At a later time you will decide how the data will be displayed.
And don't be wary. Even if Access is impartial it has tools that allow you to change the background color for the records that store an "born in UK" worker. Even is possible to instruct Access: If the user click in a record where you store an "born in UK" worker then pop up an window and, in this window, write this: THIS IS THE BEST WORKER BECAUSE... HE IS BORN IN UK.

pr2-eugin · Nov 12, 2013

Brianwarnock said:
Come on now Paul.....

Brian, I value and agree your opinion that this could be a design issue.. Although, without knowing the exact requirement of why this is needed no one can jump into conclusion..

If we default everyone to UK and have only one question "Country Of Birth", nothing wrong with that. BUT what happens if the Country of Birth is Unknown? Would that not make his birth place to be UK (by default)? How about a person born in India, but naturalized to UK? Would that still follow the same logic?

David R · Nov 12, 2013

Just add [Country of Birth] value "UNK", or 000, or whatever...

Mihail · Nov 12, 2013

@pr2

BUT what happens if the Country of Birth is Unknown?

What about a new country on the map ? The _Unknown country.
The underscore is in order to be the first on AZ order.

pr2-eugin · Nov 12, 2013

Mihail said:
The underscore is in order to be the first on AZ order.

Mihail, I am not saying what I gave is the right solution and whatever you say is wrong..

What I said to Brian goes to you too.. I value your opinion although..

pr2-eugin said:
...without knowing the exact requirement of why this is needed no one can jump into conclusion..

I am merely explaining why I said "I would not class that as an obvious"..

Mihail · Nov 12, 2013

@pr2

Mihail, I am not saying what I gave is the right solution and whatever you say is wrong...

Paul !!!!!!!!! Just I give an answer to your question. Good or bad. Here is a brainstorming. So is far a way from my intention to combat you or others.
After I post I saw that David R already post the same. Using, of course, other words.

Brianwarnock · Nov 12, 2013

Paul

I forgot one of the most important rules of teaching

" nothing is obvious to one who does not know"

I apologise.

Brian

Elmobram22 · Nov 28, 2013

Wow,

You boys have been busy!! I have spent time learning the cascading combos and that has helped with other aspects of my database design so I thank you for that. Sure there are easier ways to do it but I'd prefer to better my knowledge so that in future I can design something based on specifics rather than doing what comes into my head straight away. Anyway appreciate the feedback and thanks for the help.

Cheers,

Paul

Mihail · Nov 28, 2013

Good for you. Very good. And you will be welcome any time.
Good luck !

Iif statement to add value in next column (1 Viewer)

Registered User.

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Retired

Registered User.

Super Moderator

Retired

Registered User.

Super Moderator

I know a few things...

Registered User.

Super Moderator

Registered User.

Retired

Registered User.

Registered User.

Similar threads

Users who are viewing this thread