IIF statement

[awake2424]

Hello,

In Access 2003 is there a way to enter 16 different IIF statements? I have 14 entered but when I try to add any more I get a formula too complex. Thanks.

 

[MyTech]

If you use code, you might look into "Select Case" method.

 

[awake2424]

I was looking into that but am not sure how to format it correctly:

I have 16 names that I want to populate a # when a specific Name is selected:

For example:
JOHN 1
Sara 2
Paul 3

Name Field is John

# Field should be 1

Thanks.

 

[boblarson]

You don't use IIF's for this. I'm not understanding exactly what your aim is here. What is the ultimate goal. And not just that you want numbering. What is it for?

 

[MyTech]

Here's a sample to play with:


Private Sub Command24_Click()
Select Case InputBox("Name")
Case "JOHN"
MsgBox "1"
Case "Sara"
MsgBox "2"
Case "Paul"
MsgBox "3"
End Select
End Sub

 

[awake2424]

Is there a way to then have that result be pasted to the # field? Thanks.

 

[MyTech]

Case "JOHN"
NumbeField = 1
Case "Sara"
NumbeField = 2
Case "Paul"
NumbeField = 3



"NumberField" stands for the name of the field where you want to save the number to.

 

[awake2424]

Is there a way that based on what is selected in the Name field that the # field can be populated. So if "John" appears in the Name field in the # field 1 appears? Thanks.

 

[boblarson]

First off I am having a hard time understanding why you would have something like this. If you have consistent numbering put the names and numbers in a table and then use a query to get it. Hard coding this is ludicrous.

 

[boblarson]

And I am wondering why MyTech seems intent on encouraging bad programming practices.

 

[awake2424]

Where do I make this table and how do I point the field to it? Thanks.

 

[boblarson]

Where do I make this table and how do I point the field to it? Thanks.

Well, first I would like you to answer my question that I keep asking but you have not answered. I don't have any clue as to what you are talking about when you say "how do I point the field to it?" So, can you take a few moments and describe what this is all for - why you need to put in a number for a particular person and where you are planning on using it. Then I might be able to give you better guidance on how you would set this up.

But if you just keep on in the path you are on - (not giving any useful information) we don't stand a chance of giving you good info (Garbage in, garbage out).

 

[awake2424]

I have a list of 16 names and 16 numbers. What I was trying to do was have a field called name in a form and next to it a field controlled by an IIF statement. Each name has a specific number associated with it, but that is too many criteria for an IIF. So if I make a table with all the names and numbers in it, can it be used for a match based on the name field. So of John is selected in thee name field that is used to search the table of names. Can the resulting number be outputed in the form? Thanks.

 

[Beetle]

You still haven't really answered the question. It's clear that you want to select a name and have a number appear in another control on a form, or get populated to a field, or something, but the question is why. Bob isn't asking these questions just because he's nosy. He's asking because it's possible that you're trying to do something that's uneccessary or ill-advised and there may be a better way to approach whatever it is you're trying to accomplish.The example data you posted is presumably just an oversimplification of the actual data you are working with. However, let's walk through a scenario using that example data.

If I have a table, called tblNames, with data like the following;

TheName    TheNumber
John            1
Sally            2

I could then create a simple form with a combo box and a text box. The Row Source of the combo box would be;

Select TheName, TheNumber From tblNames;

Then in the Control Source of the text box I could put;

=[MyComboBox].Column(1)

Now when I select John in the combo box the number 1 will appear in the text box. When I select Sally from the combo box the number 2 will appear in the text box. No convoluted IIf statement (or anything else) necessary.

However, we're still not sure if this is actually what you are trying to do or, more importantly, why?

 

[MyTech]

Hey Bob.

I respect you for all the help and advice you give for me and others.


I might still be a 'bad' [vs. 'learning'] programmer, but it does not mean that I shouldn't put my 2 cents into a post that was ignored for 20 minutes, while some are ignored forever. If anyone else has any better idea, he is welcomed and appreciated to educate the asker AND the 'bad' replier altogether.
And sometimes, beginning programmers might be in a rush and want to finish up what they are in middle of doing with quick code they understand - even if it is non-professional - rather than sitting down and learning some new methods they didn't experience yet. On another shiny day - when they will have more time - they will ambitiously learn the professional way and all its methods.


And in short, dear Bob, please, "teach me, don't shout me".

 

[boblarson]

And that answer answers why you posted what you posted. I was wondering. Sorry if it was kind of harsh. I was thinking that you had more experience than you have and I apologize for writing what I wrote.

So, for teaching - here's one for you:

Always do your best to not hard-code anything in your database. It should be possible to do and if you ever get stuck and can't figure out how to get around that, post the question. Because that right there will make your life, and the lives of those who follow on afterwards, easier since you (or they) will not have to touch code to make changes.

And as far as it not being answered in 20 minutes, it would be of concern if it were 6 hours, maybe. But yes, there are threads that go unanswered. Many times it is because it is either not something that one of the volunteers here has knowledge of, or they don't want to attempt to answer because it likely will lead them down a path which they can't do at this point (time that is). But we answer what we can and I appreciate that you tried to do something about it. I am sorry in that I should not have posted that and should have probably sent you a PM explaining. So, I learned something here too.

Thanks

 

[MyTech]

I admire your reply Bob, I thank you for your advice which keeps on getting me better, and I value your nature that when you help somebody, you go the whole path down to understand his needs and providing the right solution.

But I was different, I saw he was using IIF for 16 sticky cases, so I just told him 'if' he was doing the IIF in 'code', then 'Select Case' would be for him much easier to understand and work with.
I expected him to ask 'how's and 'but's until he will be satisfied or I'll understand better his needs and provide a different method (or give up).

Anyway, the main beauty is that people try to - and do - help each other.