Solved Combining IIf Statements (6 Viewers)

[Gasman]

Now crossposted at https://www.utteraccess.com/topics/2066757?post=unread

 

[dullster]

We got it worked out. I want to post the end result expression then I'm marking this a solved.

IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]>25727,[Basic Salary]-[Lower],IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]>17780,[Basic Salary]-[Lower],[Amount from Column A]))

 

[plog]

The saga continues:

Make a negative number a "0"

I have a query expression with some of the results being a negative numbers that I wish to change to a 0. I have Salaries minus several deductions created several "Employees" to be negative which means they get a standard deduction so I want the results to be 0 instead of negative. Net Wage...

www.access-programmers.co.uk

 

[dullster]

Thank you for remembering me. Actually it doesn't continue, I have another question about the DB.

I'm starting to find any new questions that I have, may not be welcomed here or at least by you.

Either way let me know and if you prefer not to help, I'll find somewhere else for help. Thank you plog.

 

[CJ_London]

@dullster I think the problem is you ask vague questions with incomplete data and approach a problem one step at a time without giving context to the overall requirements. As a result your threads have 40+ posts when it is likely it could have been resolved with 3 or 4 posts, saving you and us time.

 

[dullster]

My apologies

 

[dullster]

Now crossposted at https://www.utteraccess.com/topics/2066757?post=unread

I seems that no matter what I post, I'm going to get criticized instead of helped.

 

[dullster]

I second the motion.

@dullster Assume that your already-somewhat-complex statement will grow in complexity over time. A VBA function will make it much easier to read, edit document and maintain. As soon as I have significant trouble reading a statement like that I switch to VBA , if there's no other reason not to. If the Access IDE allowed you to format code in those situations and preserved that formatting and coloring, it might be a different story...

I would love to switch to VBA. I'm never written one before. What is my first step or should i watch videos?

 

[June7]

Hard to say. What do you already know about programming? Ever programmed in any language? The basic concepts are transferrable.

Start with review of https://support.microsoft.com/en-us...gramming-92eb616b-3204-4121-9277-70649e33be4f

It's considered good etiquette to inform readers when you post same question in multiple forums.

 

[Pat Hartman]

I seems that no matter what I post, I'm going to get criticized instead of helped.

I hate to add to your discomfort but the essential problem is the cross-posting. The experts here are all volunteers. No one is getting paid to help you. When you can't be bothered to wait for an answer on a single forum and so post in another, you start a second group of experts looking at your problem. This wastes the time of the both groups and shows great disrespect for their efforts on YOUR behalf. That is why you are getting complaints. Plus your questions tend to be vague which always makes our task harder.

We welcome your questions and are happy to spend time researching for you and helping out but please respect our efforts.

 

[The_Doc_Man]

@dullster - I speak to you now in my role as a moderator. I saw your complaint and I think I understand it.

One of the comments made in this thread is that you cross-posted a question without notification. What that specifically means is that you posted a question in more than one site and didn't tell us that you had done so. There is a solution on the cross-posting site, but you have failed to notify us of that solution being available.

Let me be clear: It is not illegal to cross-post. However, we consider it somewhat insulting and unappreciative to do so without telling us. The reason is that, as Pat has explained, we volunteer our time to help people. We have seen many times that people get frustrated by not getting an answer (or at least not getting an answer they like), so they resort to asking around other specialty sites like ours. It might seem that you would have a better chance getting an answer by putting your question on a second site. But many of our experts post on multiple sites and recognize your problem because they saw it on another site.

Cross-posting is a sign of a couple of problems at once.

First, you don't think you are being helped. But if there is either a language barrier OR a nomenclature barrier, it might be that we are facing the REAL problem, which I'm beginning to think is applicable here. From the movie Cool Hand Luke, "What we have here is a failure to communicate." Your descriptions make it difficult for us to understand the problem. You keep on saying you get an error. Forgive me for this, but the error is in you stating it that way. You should say "When I do x y z I get error nnnn, Syntax error" I.e. tell us what you did and the error you got. Let me give you an analogy. You go to the doctor and you tell him/her "I am not feeling well." So the doctor asks questions and - due to confusion, perhaps - you don't provide anything more specific. Do you really expect the doctor to identify what is wrong? Without some indication of HOW you know something is wrong, we are as in the dark as you are.

Second, you don't realize OUR situation, where we get literally hundreds of posts in a busy day and need to respond to them. BUT this is truly a world-wide forum with members in Japan and the Philippines, Australia and New Zealand, North and South America, most of the European countries, Africa, and many nations on the Asian Continent. Sometimes you might have to wait for help due to time zone differences. So your impatience ends up alienating folks who really DO want to help you.

When you cross-post without telling us about it, then something happens that REALLY irks us. You get an answer on the other site (which HAS happened) and you don't tell us the problem has been solved (which also HAS happened), thus letting us spin our wheels on something that is no longer unsolved. That is disrespectful. If you believe you are being disrespected, please consider it as a reflection of your own behavior.

If you want to continue to ask questions of this forum, just PLEASE understand that we are people too, and we have time limits on how much time we can spend on any one problem. We can forgive you for the insult. But can you forgive us for that most horrible insult - that we didn't understand what you were doing based on the way you described it?

 

[dullster]

@dullster - I speak to you know in my role as a moderator. I saw your complaint and I think I understand it.

One of the comments made in this thread is that you cross-posted a question without notification. What that specifically means is that you posted a question in more than one site and didn't tell us that you had done so. There is a solution on the cross-posting site, but you have failed to notify us of that solution being available.

Let me be clear: It is not illegal to cross-post. However, we consider it somewhat insulting and unappreciative to do so without telling us. The reason is that, as Pat has explained, we volunteer our time to help people. We have seen many times that people get frustrated by not getting an answer (or at least not getting an answer they like), so they resort to asking around other specialty sites like ours. It might seem that you would have a better chance getting an answer by putting your question on a second site. But many of our experts post on multiple sites and recognize your problem because they saw it on another site.

Cross-posting is a sign of a couple of problems at once.

First, you don't think you are being helped. But if there is either a language barrier OR a nomenclature barrier, it might be that we are facing the REAL problem, which I'm beginning to think is applicable here. From the movie Cool Hand Luke, "What we have here is a failure to communicate." Your descriptions make it difficult for us to understand the problem. You keep on saying you get an error. Forgive me for this, but the error is in you stating it that way. You should say "When I do x y z I get error nnnn, Syntax error" I.e. tell us what you did and the error you got. Let me give you an analogy. You go to the doctor and you tell him/her "I am not feeling well." So the doctor asks questions and - due to confusion, perhaps - you don't provide anything more specific. Do you really expect the doctor to identify what is wrong? Without some indication of HOW you know something is wrong, we are as in the dark as you are.

Second, you don't realize OUR situation, where we get literally hundreds of posts in a busy day and need to respond to them. BUT this is truly a world-wide forum with members in Japan and the Philippines, Australia and New Zealand, North and South America, most of the European countries, Africa, and many nations on the Asian Continent. Sometimes you might have to wait for help due to time zone differences. So your impatience ends up alienating folks who really DO want to help you.

When you cross-post without telling us about it, then something happens that REALLY irks us. You get an answer on the other site (which HAS happened) and you don't tell us the problem has been solved (which also HAS happened), thus letting us spin our wheels on something that is no longer unsolved. That is disrespectful. If you believe you are being disrespected, please consider it as a reflection of your own behavior.

I marked it as solved which I thought was what I was supposed to do but If I'm supposed to do something else, please let me know. I did that because one person just keeps making rude comments about doing it sql, which i don't know how to do and that really is their only contribution which just keeps adding to the thread. When I try to clarify, they continue making more non helpful comments so I'm spinning my wheels too. I was just learning to navigate the forum and apparently made some mistakes and I'll own that.

I went to a new forum with a new question to try to get away from them and guess who followed me there. My question wasn't even the same question but it didn't seem to matter, it appears no matter what I ask they will proceed to comment. I wanted help but don't need badging. If someone doesn't like that, I don't understand they could scroll on by instead of adding to continued thread to make comments that aren't helpful. What could be a question and simple answer 2 thread forum, turns into a 4 thread with their unneccessary comments. I guess I don't know how to ask any thing anymore because nothing I say makes them stop.

I really appreciate the help and most people are helpful. I have met someone on there that I'm in contact with directly to work through some problems. I hope this explains why I went to another forum as it was a completely different question. I would love to continue to get help as needed and as I have if the hackling stops. I will try again but it is unneccessary for them to continue making empty comments to expand a thread.

Thank for your contact.

In fact, I too see questions that I could answer so i might help in that context.

 

[Isaac]

I would love to switch to VBA. I'm never written one before. What is my first step or should i watch videos?

I'd say make a post with a simple example of what you'd want to make and I'll help show a VBA version of it. Just for time's sake, a simple one

 

[The_Doc_Man]

I will state an observation here, that you are in that stage where you are finding out the power of Access via its Graphic User Interface - the GUI - but you are also apparently butting up against its limits because you are not yet comfortable with VBA. AND you have claimed a limited ability with SQL. To be honest, EVERYONE runs into a moment like that. Perhaps not specifically with Access, but basically you have just opened up Pandora's Box and now the evils of the (Access) world are emerging to tantalize you.

There is, indeed, a steep learning curve and that is at least partly because the theory behind relational databases can be rather complex. I cannot tell you what to read, though, because everyone has their own favorite - or at least, most comfortable - way of learning. You might need to go find a book store with a decent technical section and see if there is anything on Access. Quickly page through any that you find to see if you "connect" with what they are saying. If you CAN connect to the author's way of expressing things, THAT is the book you should read. And unfortunately, the one that connects with me won't necessarily connect with you. And vice-versa.

For future reference, just remember that we CAN discuss generalities, but we DIAGNOSE based on specifics. If you see something you think you can answer, you ARE a member now. Jump in. Just remember that giving answers requires equal clarity as asking questions.

 

[dullster]

I'd say make a post with a simple example of what you'd want to make and I'll help show a VBA version of it. Just for time's sake, a simple one

Is this too simple?

IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]>25727,[tblEmployees].[Basic Salary]-(([tblEmployees].[Basic Salary]-[Lower])*0.2)-([Exemption credit]),IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]>17780,[tblEmployees].[Basic Salary]-(([tblEmployees].[Basic Salary]-[Lower])*0.12)-([Exemption credit])))

 

[Isaac]

Good, I will write up a potential vba function if some overachiever type doesn't try to beat me to it, tomorrow morning or later tonight

What are the reliable datatypes of input columns Marital Status, Basic Salary, Lower and Exemption credit? I was kind of guessing Whole number, Decimal/double to start with, not sure about the last two. And can they be null potentially - as in, do you allow nulls at the table level?

 

[The_Doc_Man]

You deserve at least a direct answer or two. From your first post:

IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]), IIf([tblEmployees].[Marital Status]=2,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0))

I get this error, The expression you entered has a function containing the wrong number of arguments. What am i doing wrong?

I tweaked you earlier on not showing the error message but you actually had it. It just didn't stand out. So for the tweak, I apologize.

The specification for IIF is IIF( criteria-expression, true-result-expression, false-result-expression) where you are trying to pick between two alternatives to get one (and only one) of two possible answers. The syntax rules are that the criteria-expression SHOULD resolve to either a TRUE or a FALSE result. Then the function returns the result of evaluating either the true-result-expression or the false-result-expression. The two result-expressions should in theory BOTH return the same data type, which will become the effective data type of the IIF result. Actually, most intrinsic functions (like IIF) return variant data types, so it doesn't really matter what data type those two expressions return.

The way to analyze this kind of error (involving functions) most often starts by grouping expression elements by parentheses and commas.

IIf( [tblEmployees].[Marital Status]=1, <== will return True/False - so meets criteria-expression requirement.
[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower], <== because of the ">", a relation operator, this expression will return True/False.
([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]), <== will return a number based on data type of [Basic Salary] and [Lower] which means the two result expressions don't match in datatype. One is T/F, the other is numeric. That is already not a good thing, though VBA can handle that by doing something called "type coercion".

Even more important, that false-result-expression ends with a comma. which signals VBA that another argument is coming. Except that you have already had three arguments, which is all that IIF condones. Thus your error "wrong number of arguments."

 

[dullster]

Good, I will write up a potential vba function if some overachiever type doesn't try to beat me to it, tomorrow morning or later tonight

What are the reliable datatypes of input columns Marital Status, Basic Salary, Lower and Exemption credit? I was kind of guessing Whole number, Decimal/double to start with, not sure about the last two. And can they be null potentially - as in, do you allow nulls at the table level?

Marital Status is from table [Marital Status] Married=1, Single=2 Short text and is required; Basic Salary is from [tlbEmployees] Currency is required; Lower is from [tblpayrolltaxes] Currency is required; [Exemption credit] if a calculated Currency field in the [QryStateTax]

I have all currency set to 2 decimals so it has to round. For these fields, they can not be null, they at least have to have a "0" for calculation in the future.

I hope that answers all your questions. Let me know if i missed anything or answered incorrectly.

Thank you so much for this. I have some if statements that when they are nested, will not calculate right so I pretty sure I will have to do them in vba.

 

[dullster]

You deserve at least a direct answer or two. From your first post:

IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]), IIf([tblEmployees].[Marital Status]=2,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0))

I tweaked you earlier on not showing the error message but you actually had it. It just didn't stand out. So for the tweak, I apologize.

The specification for IIF is IIF( criteria-expression, true-result-expression, false-result-expression) where you are trying to pick between two alternatives to get one (and only one) of two possible answers. The syntax rules are that the criteria-expression SHOULD resolve to either a TRUE or a FALSE result. Then the function returns the result of evaluating either the true-result-expression or the false-result-expression. The two result-expressions should in theory BOTH return the same data type, which will become the effective data type of the IIF result. Actually, most intrinsic functions (like IIF) return variant data types, so it doesn't really matter what data type those two expressions return.

The way to analyze this kind of error (involving functions) most often starts by grouping expression elements by parentheses and commas.

IIf( [tblEmployees].[Marital Status]=1, <== will return True/False - so meets criteria-expression requirement.
[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower], <== because of the ">", a relation operator, this expression will return True/False.
([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]), <== will return a number based on data type of [Basic Salary] and [Lower] which means the two result expressions don't match in datatype. One is T/F, the other is numeric. That is already not a good thing, though VBA can handle that by doing something called "type coercion".

Even more important, that false-result-expression ends with a comma. which signals VBA that another argument is coming. Except that you have already had three arguments, which is all that IIF condones. Thus your error "wrong number of arguments."

I didn't know that the IIF only condones three arguments. Probably exactly my problem, thus why I need to graduate to vba. Thank you.

 

[DHookom]

I didn't know that the IIF only condones three arguments. Probably exactly my problem, thus why I need to graduate to vba. Thank you.

I took one of your expressions and began creating a function with comments about the process I go through in your other thread. You need to start the conversion and a new thread when you begin to stumble (we all do).