Show results in a querrie

[Guest]

I have a querrie, which it add up some fields. I would like all the results
that is over ten to be equal with ten. For example if the result is 11, the
qerrie shows it as 10...How I do that? I think to insert in Expression
Builder an "if" relation, but i dont do anything...

 

[Brendan Reynolds]

In queries, you need to use the IIf (Immediate If) function ...

SELECT IIf([TestNumber]<11,[TestNumber],10) AS Expr1
FROM tblTest;

In query design view, that would look like ...

Expr1: IIf([TestNumber]<11,[TestNumber],10)

Change 'TestNumber' to the name of your field, and 'Expr1' to whatever you
want to call the calculated column in the query.

See 'IIf Function' in the (VBA) help file for details.

 

[Guest]

Dear Brendan Reynolds,

I say all that you say, but .
I wrote
IIf([Τελική Βαθμολογία 1ου μαθήματος]![Expr2]>10;10;[Τελική Βαθμολογία 1ου
μαθήματος]![Expr2]) and the results were that all the fields of Expr2 which
were over ten (11, 11.5, 12 etc...), has dissappeared!!!
We are in the right direction, but I want the results which are over ten
appear as ten.

 

[Brendan Reynolds]

Is this a summary (totals) query? If so, make sure that 'Expression' is
selected in the Total row for this column.

 

[Guest]

Dear Brendan,
When i try to make real yor advice i take the message:
"You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression
as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement. "

*******************************************************
NOW:
I bielieve that the querry isn`t summary. I have a field with name, another
field with ID, and the "noisy" field Expr2, where i add five fields of 1st
query and also i add another field from a third table....i am so confused!!!

 

[Brendan Reynolds]

Can you post the SQL for the query? I'm not sure whether I'll be able to
figure it out, but if you post it I'll try. (If you're not sure how, just
ask.)

 

[Guest]

Dear Brendan,
I would like to thank you for all your time to spend to help me....
So I paste the code ouf the Quirre:
SELECT [Τελική Βαθμολογία 1ου μαθήματος].Μάθημα, [Τελική Βαθμολογία 1ου
μαθήματος].[Ονοματεπώνυμο Σπουδαστή], [Τελική Βαθμολογία 1ου μαθήματος].ΑΕΜ,
[Τελική Βαθμολογία 1ου μαθήματος].Expr2
FROM [Τελική Βαθμολογία 1ου μαθήματος]
WHERE ((([Τελική Βαθμολογία 1ου μαθήματος].Expr2)=IIf([Τελική Βαθμολογία 1ου
μαθήματος]![Expr2]>10,10,[Τελική Βαθμολογία 1ου μαθήματος]![Expr2])));

I hope the greek not be problem to understand the good.Also i would like to
inform you that i create a new qyertry which i have inside all the fields of
the previous querrie, because if i paste here the first querriy you may be
see a mess...Also this querriy continues to present only the results wich is
under ten...So read the code and please try to find something!!!!

 

[Brendan Reynolds]

I am finding this quite difficult to read (not your fault, of course, just
the language barrier) but I think the problem is that the IIf expression is
in the WHERE clause. If you don't want the expression to limit the rows that
are returned, it should be in the SELECT clause. It might look something
like ...

SELECT [?????? ?????????? 1?? ?????????].??????, [?????? ?????????? 1??
?????????].[????????????? ?????????], [?????? ?????????? 1??
?????????].???,
[?????? ?????????? 1?? ?????????].Expr2, IIf([?????? ?????????? 1??
?????????]![Expr2]>10,10,[?????? ?????????? 1?? ?????????]![Expr2] AS
Whatever
FROM [?????? ?????????? 1?? ?????????];

In query design view, you would enter the expression in a new column, in the
Field row, not in the criteria row, looking something like ...

Whatever: IIf([?????? ?????????? 1?? ?????????]![Expr2]>10,10,[??????
?????????? 1?? ?????????]![Expr2]

.... replacing "Whatever" with what ever title you want to give to the
calculated column.

 

[Brendan Reynolds]

Did I say 'language barrier'? I guess I forgot about the 'code-page
barrier'! :-(

--
Brendan Reynolds (MVP)

I am finding this quite difficult to read (not your fault, of course, just
the language barrier) but I think the problem is that the IIf expression is
in the WHERE clause. If you don't want the expression to limit the rows
that are returned, it should be in the SELECT clause. It might look
something like ...

SELECT [?????? ?????????? 1?? ?????????].??????, [?????? ?????????? 1??
?????????].[????????????? ?????????], [?????? ?????????? 1??
?????????].???,
[?????? ?????????? 1?? ?????????].Expr2, IIf([?????? ?????????? 1??
?????????]![Expr2]>10,10,[?????? ?????????? 1?? ?????????]![Expr2] AS
Whatever
FROM [?????? ?????????? 1?? ?????????];

In query design view, you would enter the expression in a new column, in
the Field row, not in the criteria row, looking something like ...

Whatever: IIf([?????? ?????????? 1?? ?????????]![Expr2]>10,10,[??????
?????????? 1?? ?????????]![Expr2]

... replacing "Whatever" with what ever title you want to give to the
calculated column.

--
Brendan Reynolds (MVP)

Dear Brendan,
I would like to thank you for all your time to spend to help me....
So I paste the code ouf the Quirre:
SELECT [?????? ?????????? 1?? ?????????].??????, [?????? ?????????? 1??
?????????].[????????????? ?????????], [?????? ?????????? 1??
?????????].???,
[?????? ?????????? 1?? ?????????].Expr2
FROM [?????? ?????????? 1?? ?????????]
WHERE ((([?????? ?????????? 1?? ?????????].Expr2)=IIf([?????? ??????????
1??
?????????]![Expr2]>10,10,[?????? ?????????? 1?? ?????????]![Expr2])));

I hope the greek not be problem to understand the good.Also i would like
to
inform you that i create a new qyertry which i have inside all the fields
of
the previous querrie, because if i paste here the first querriy you may
be
see a mess...Also this querriy continues to present only the results wich
is
under ten...So read the code and please try to find something!!!!

 

[Guest]

Dear friend,
It works!!!!
thanks for your help
But it doesnt work in the querrie where i make the adding. I must create
another querriy. Maybe because the adding was so big

 

[Nikos Yannacopoulos]

Brendan, Andreas,

PMFJI, I thought I might be of some assistance since I don't have a
language/codepage barrier here.

Andreas, try this:

SELECT ÌÜèçìá, [Ïíïìáôåðþíõìï ÓðïõäáóôÞ], ÁÅÌ, IIf([Expr2] > 10, 10,
[Expr2]) As Expr3
FROM [ÔåëéêÞ Âáèìïëïãßá 1ïõ ìáèÞìáôïò]

Where I assume Expr2 is the name of a calculated field in query [ÔåëéêÞ
Âáèìïëïãßá 1ïõ ìáèÞìáôïò]? If not, then change Expr2 to the name of the
field you are applying the IIf on.

HTH,
Nikos