AND keyword in the <true value> part of an IIF function. (1 Viewer)

[smbrr]

Hello,

what i'm trying to do is simple: the user types "201401", and the query has to understand that it's in the 1st quarter, so the period selected will be 201401-201402-201403.

Simple, but I can't do it. I tried with a switch function at first but it didn't work so i moved on nested IIF's, but still no dice.

What i don't want to do:
- give any more parameters (that one [per] is used in many queries that are all linked together)
- put my vars in a form, because i don't want to rely on having a form open, and mostly i don't my queries to stop functionning if the form is deleted/altered
- make a vba module. I can program, but I want these queries to run on their own.

Without furder ado: my expression:

IIf(Int(Right([per];2))<4;>Left([per];4)*100 And <Left([per];4)*100+4;
IIf(Int(Right([per];2))<7;>Left([per];4)*100+3 And <Left([per];4)*100+7;
IIf(Int(Right([per];2))<10;>Left([per];4)*100+6 And <Left([per];4)*100+10;
IIf(Int(Right([per];2))<13;>Left([per];4)*100+9 And <Left([per];4)*100+13))))

The result is simply empty. I tested separately every piece of it, and it works, but all together it won't even give me my Q1 2014 for "201401", just a blank result.

As the title says, I think i'm messing up the AND keyword somehow.

Thanks in advance.

 

[BlueIshDan]

Excuse my ignorance, but pardon me if I'm wrong. Why are you using ; instead of , in your Left and Right functions?

 

[smbrr]

I made it in the expression builder, and it told me syntax was wrong when i used commas. It must be right since it runs.
For example, i take the ">Left([per];4)*100 And <Left([per];4)*100+4" and run it on its own, i get the desired result.

 

[BlueIshDan]

Well then... Would you be willing to give me your entire query?

 

[smbrr]

Sure, but my problem lies entirely in this one expression. I'm trying to make it work in a plain new query with no joins or anything, just to select the quarters.

Here is the query it's meant to be used in;

SELECT Data.ClientID, Data.SorgID, Data.DivID, Data.BrandID, Data.DistChanID, Data.SourceID, Sum(Data.Qty) AS SumOfQty, Sum(Data.Val) AS SumOfVal
FROM Data
WHERE (((Data.Periode)=IIf(Int(Right([per],2))<4,(Data.Periode)>Left([per],4)*100 And (Data.Periode)<Left([per],4)*100+4,
IIf(Int(Right([per],2))<7,(Data.Periode)>Left([per],4)*100+3 And (Data.Periode)<Left([per],4)*100+7,
IIf(Int(Right([per],2))<10,(Data.Periode)>Left([per],4)*100+6 And (Data.Periode)<Left([per],4)*100+10,
IIf(Int(Right([per],2))<13,(Data.Periode)>Left([per],4)*100+9 And (Data.Periode)<Left([per],4)*100+13))))))
GROUP BY Data.ClientID, Data.SorgID, Data.DivID, Data.BrandID, Data.DistChanID, Data.SourceID;

The plan is to have a sum of the "qty" and "val" from table "data" for the given quarters, and this result is joined with other tables down the road.

 

[BlueIshDan]

There we go, it looks liek it makes a little more sense now haha. let me take a minute to look

 

[BlueIshDan]

Hmm your IIF Functions look to be a little off. Have you used many?

IIF(condition, Value if True, Value if False)
Yours seems to be IIF(condition, condition, condition... then you have lost me. * )

 

[BlueIshDan]

If you're fond of vba, you might have a better time writing a public function that recieves your (Data.Periode) and [per] values and returns what you're trying to parse in your WHERE clause...

 

[smbrr]

You got it, it's my first time using IIF.

What i made in the expression builder is iif(condition, value if true, iif(condition, value if true, iif( ...

and so on.

Now that I posted the SQL for you though, I'm starting to see inconsistencies.

MS Access translated my expression in

(Data.Periode)=IIf(Int(Right([per],2))<4,(Data.Periode)>Left([per],4)*100 And (Data.Periode)<Left([per],4)*100+4 ...

which is bound to fail... because the result of this expression can only be one thing, and I put an AND operator in there, so I have 2 results.

So SQL is trying to do

data.periode = >201400 AND <201404

And that doesn't even makes sense, programatically.

But then why don't I get an error on run?

i'm getting confused.

Maybe I should trash the expression builder and directly work in the SQL like so:

SELECT... FROM data
WHERE IIF(condition, value1 if true AND value2 if true, IFF(...

Does that make any sense?

 

[namliam]

You cant use IIF in a where cluase like that to generate it...

Try writing the Where like so (for the first IIF)

where int(right(PER,2))<4 and periode > Left([per],4)*100 and periode < Left([per],4)*100+4
or ...

 

[smbrr]

But in this case I lose the whole

if condition then value

structure and turn it into a three-way where clause, don't I?

Or do you mean

where int(right(PER,2))<4

acts the same as an

if int(right(PER,2))<4

??

I never heard of that.

Maybe this is getting too complicated, isn't there a simple template method for this kind of situation? Lots of people probably tried to do something similar.

 

[namliam]

What I think you are trying to do is
If the int(per) < 4 use this where clause
(Data.Periode)>Left([per],4)*100 And (Data.Periode)<Left([per],4)*100+4
if int(per) < 7 ... etc...

That is what the OR does, btw I forgot the brackets...

where ( int(right(PER,2))<4 and periode > Left([per],4)*100 and periode < Left([per],4)*100+4 )
or ...

The brackets DO matter a lot...
In essence it will do 4 and ....
7 and...
10 and ...
etc...
Only one fo the lines will "fit"

 

[namliam]

Essentially this could be brought down to only one where clause if you try hard enough:

IIf(Int(Right([per],2))<[B]4[/B],(Data.Periode)>Left([per],4)*100 And (Data.Periode)<Left([per],4)*100+[B]4[/B],
IIf(Int(Right([per],2))<[B]7[/B],(Data.Periode)>Left([per],4)*100+3 And (Data.Periode)<Left([per],4)*100+[B]7[/B],
IIf(Int(Right([per],2))<[B]10[/B],(Data.Periode)>Left([per],4)*100+6 And (Data.Periode)<Left([per],4)*100+[B]10[/B],
IIf(Int(Right([per],2))<[B]13[/B],(Data.Periode)>Left([per],4)*100+9 And (Data.Periode)<Left([per],4)*100+[B]13[/B]))))))

Given that these numbers keep repeating
Something like

(Data.Periode)>Left([per],4)*100+[B]int(right(per,2))-4[/B] And (Data.Periode)<Left([per],4)*100+[B]int(right(per,2))[/B]

would seem to fit the bill?

 

[smbrr]

Putting everything in one expression is too tricky because for example 201401 will become 201400 + (01-4) so 201400+(-3) = 201397 ...

Anyway, I think the OR statement will do the trick. I tried on a couple and it worked, so now I just gotta make the 4 conditions.

I didn't know you could use where on a parameter like that, so the OR clause wasn't even a solution for me/

Thank you a lot guys! very nice people.

 

[namliam]

True my "quick" fix was to quick, but it is still doable.... Perhaps using an IIF or some cleaver devisions and rounding stuff.

But if the OR contruct will work for you that is easier and much more pleasing on the eye as well as more reproducable into future simular problems.

 

[BlueIshDan]

This is why I suggested passing the value to a function. Looks like something that could be used in more than just a query.

 

[namliam]

Didnt really occure to me untill now, you are basicaly doing quarters
Though if you are doing quarters this where clause doesnt really make much sence to me...

 

[smbrr]

How else would you do it? My users type 201401 and I have a dozen queries running on it with different variation. Some take the full year, other the month, other the month of last year, etc. And this one is the quarter one.

 

[namliam]

Yeah but you are doing a where clause that will eventually allow each and every quarter.
Unless PER is actually a parameter to the query, which I was assuming to be a column from one of the tables sourcing the query. If PER is a parameter, then yes that makes (more) sence.

 

[smbrr]

Yes yes, [per] is the parameter passed in the final query and in all the subqueries.

In the end, my query looks like that: (i can't post links apparently i'm too new so just append it to http)

i58.tinypic.com/141p0yv.png

in sql:

SELECT Data.ClientID, Data.SorgID, Data.DivID, Data.BrandID, Data.DistChanID, Data.SourceID, Sum(Data.Qty) AS SumOfQty, Sum(Data.Val) AS SumOfVal
FROM Data
WHERE (((Right([per],2))>9 And (Right([per],2))<13) AND ((Data.Periode)>Left([per],4)*100+9 And (Data.Periode)<Left([per],4)*100+13)) OR (((Right([per],2))>6 And (Right([per],2))<10) AND ((Data.Periode)>Left([per],4)*100+6 And (Data.Periode)<Left([per],4)*100+10)) OR (((Right([per],2))>3 And (Right([per],2))<7) AND ((Data.Periode)>Left([per],4)*100+3 And (Data.Periode)<Left([per],4)*100+7)) OR (((Right([per],2))>0 And (Right([per],2))<4) AND ((Data.Periode)>Left([per],4)*100 And (Data.Periode)<Left([per],4)*100+4))
GROUP BY Data.ClientID, Data.SorgID, Data.DivID, Data.BrandID, Data.DistChanID, Data.SourceID;

And it works perfectly.
There might be a better way to do quarters, but in my situation i can't think of a better one.