IIf or not ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

In the calculation - Total: Sum(qQueryA.columnA) - the outcome can be 0,
1, 2, etc..
What I want is, that if the outcome is 0, that outcome will be changed to 1.
Else all outcomes (so 1 and higher) will be excepted as they come.

I tried all kinds of syntaxes with the IIf-function, but so far failed.
Can anyone please help me on this one ?
Thank you in advance.

Greetings
 
Hi Wayne,
Thank you for your quick response.

When I look at the code you suggest, it seems to me a very logical syntax,
but it
gave the same error-message I received on earlier attempts:
"The syntaxis of the expression that you stated was not valid" , and under
that: "for instance you stated an operand without an operator".
When I "OK" the error-message, the pointer goes to "1," (let's say the
"then-part" of the if-statement).
I hope my reaction is accurate enough for you to do an additional suggestion ?
 
Where are you placing this? The syntax you used would be for a calculated
field in a query when filling it in by using the query design view.
 
Thank you Wayne.

Yes, I'm placing it right the way you say it should be.

To test the code further, I tried this:
Total:IIf(Sum(qQueryA.ColumnA) = 0, Sum(qQueryA.ColumnA),
Sum(qQueryA.ColumnA)) ,

but even then I got the same errormessage ("not valid, operand without
operator").
When I "OK" the message, pointer goes to the 2nd "Sum".
 
Hi Wayne,

In reaction to your message I put the IIf syntax directly in the SQL view:
SELECT DISTINCTROW IIf(Sum([qQueryA].[Check])=0,1,Sum([qQueryA].[Check])) AS
Total FROM qQueryA;
(for db-reasons [Check] instead of [ColumnA])

This time I got no error message!?! and the code works correctly.
So the design view gives an error message and the sql view doesn't ...?

Off course more important is, that "it works".
Thank you for helping me on the code and bringing the sql view to my
attention !!

Greetings
 
Hi Wayne,

In reaction to your message I put the IIf syntax directly in the sql view:
SELECT DISTINCTROW IIf(Sum([qQueryA].[Check])=0,1,Sum([qQueryA].[Check])) AS
Total FROM qQueryA;
(for db-reasons [Check] instead of [ColumnA])

This time I get no errormessage !?! and the code works correctly.
So on the same code the design view gives an errormessage and the sql view
doesn't ...?

More important to me is, off course, that "it works".
Thank you very much for helping me on the code and bringing the sql view to
my attention!!

Greetings
 
The design view doesn't always get the SQL correct, but it's usually pretty
good. The SQL is what the query actually runs off of, so entering the
information there will sometimes solve weird problems.
 
Back
Top