If [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])<500 minimum charge is 50 if [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])>500 then ([weightunitised] + [weightloose])*0.07.
If [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])<500 minimum charge is 50 if [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])>500 then ([weightunitised] + [weightloose])*0.07.
I'm under the impression that you want to replace the existing IFF statement, with the one that you want to "add". Yes? If so, how about nesting:
Code:
IIF([CollectionandDelivery] = Yes, IIF([weightunitised] + [weightloose])<500, _
50, ([weightunitised] + [weightloose])*0.07), _
[I][COLOR="Red"]what value do you want here if the CollectionandDelivery option is "NO"?[/COLOR][/I])
You've got a double conditional to start with, and the IIF function only does one evaluation of a condition at a time, so in the above example, you're going to have an outcome of "" or NULL, or whatever you choose to put there for the "False" value. Will this work?
Adam - not quite sure what you mean here but it is totally possible to check multiple conditions in a single IIF statement. However, the result of that check must be one of two values. The multiple conditions must combine to be true or false. So, you can have
IIF([MyField1]>4 And [MyField2]=2 And [MyField3]<42, Whatever if all of those is true, Whatever if all is false)
But, if the statements are mutally exclusive then you have to test for each individually and have to test in the correct order (as you had mentioned) in order for them to come back correctly.
In this case it sometimes is much more useful to just create a custom function to return the correct value based on the multiple conditions. If your IIF statement starts running into the lines of:
If [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])<500 minimum charge is 50 if [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])>500 then ([weightunitised] + [weightloose])*0.07.
The point I was getting at earlier is that you want to treat both of the above statements as two different "evaluations". If that's what you want to do (and it sounds like it), you have to create two columns, with two corresponding IIF functions.
I understand this, and that is what nesting is for. That's why I first posted a sample of a nested IIF. But, it doesn't sound like that is going to work here, because both of the "different" evaluations, combined into one statement, can't give a boolean type outcome - One or the other (either that, or the possible outcomes are not what is desired).
Thank you gentlemen for your responses, I apologise for not replying straight away as today I am not at work ( I do shift work) and other duties call.
Adam while I was waiting for a reply yesterday I did wonder whether I might need another column or two, but having tried to create a function in a new column it will not work. This is where I've got to:
Code:
TransportB: IIf([LandsideCollectionandDelivery]=Yes And ([weightunitised]+[weightloose])<500,50,[TransportA])
There is nothing wrong with the second function that you've written Mac. What does that error say? I can't decipher the code called "It will not work".
It is a little known error code only part - timers like me resort to.
As you say it does work as a query as I found out when I used it in datasheet view.
My problem is getting it to "work" in my form.
The control on my form is called Transport and already has two calculated fields that interact with it. They are:
Along with the code I posted last I have added a second column as follows:
Code:
TransportC: IIf([LandsideCollectionandDelivery]=Yes And ([weightunitised]+[weightloose])>500,([weightunitised]+[weightloose])*0.02,0)
This also works in datasheet view but I need TransportB and TransportC to populate Transport when necessary. How do I do this? Do I need to use a custom function similar to Bob's suggestion in his last post?
Please clear this up for me. You have tested these IIF functions by using them for columns in a query, and by using them for controls on forms? (I want to be clear about that).
This is complicated, Mac. To give you the exact code you would have to use to do exactly what you have said you wanted to do, it would be this (this is SQL to be used for a column in a query. It is not the same as the code to be used in a form control):
Well, how do you know when it's necessary? The code I have written above is the very best you can do here just by using the IIF function and nothing else. If you don't like the outcome of what that gives you, then yes, you will have to customize a function to populate the "Transport" value.
The thing I want to point out here Mac, is the multiple combinations of possibilities you have going on here between TransportA and TransportC. The "Transport" function is obvious. If no transport is used, then the value is 0, if it is used, then start calculating the appropriate value. The problem is, the "condition" section of Transport A:
does not correlate with the "condition" section of Transport C:
Code:
TransportC: [COLOR="Red"]IIf([LandsideCollectionandDelivery] = Yes AND
([weightunitised]+[weightloose]) > 500[/COLOR],
([weightunitised]+[weightloose])*0.02,0)
Do you see how you can use the conditional section of "C" in the value if false section of "B"? You can, sure, but what would happen if [LandsideCollectionandDelivery] = no AND ([weightunitised]+[weightloose]) > 500?? You would never get the "C" result. "TransportB" is not even paying attention to the [LandsideCollectionDelivery] value. Is that value even relevant for the "B" function? It almost looks like it has to be!
That is the only other option you need to cover here (that I can see, anyway) in order to make your IIF functions work correctly. If there is no relation to the data in regards to what I just said, then a single nested IIF statement will not work for all of this.
Apologies for frustrating you, it is probably due to my lack of knowledge that we have got to this point. Perhaps we can rewind a little, I have edited my query as follows:
IIF([transportUsed] = No, 0, IIF([LandsideCollectionandDelivery] = Yes,
[TransportB], [TransportA]))
This is very simple. So, if you don't want extra columns sitting in your query called TransportA and TransportB, then just delete them and replace the references in the IIF function with the actual calculations that the fields represent.
Perhaps that should have been all is right in my world.
I am not sure this New Topic is for this section. My take on this subject is we will always have poverty in the world while mankind is greedy and only interested in himself, but some of the happiest people I've met are poor.
Perhaps that should have been all is right in my world.
I am not sure this New Topic is for this section. My take on this subject is we will always have poverty in the world while mankind is greedy and only interested in himself, but some of the happiest people I've met are poor.
And, for one last note, try this (please don't carry this one here, as it's not a forum for this purpose (you're right!)):
I read the other day that in tax Year 2006 there were like 200,000 or some people that made over $75,000 in income, and paid no income tax. Trust me Mac, I know the tricks, but do you have any idea how much of a pain in the a** it is to keep track of all that information? If I had to guess, I would say that people who do that stuff can't possibly be happy. Hell, I struggle just to keep a damn job!
