If Then Criteria

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

Guest

Hello
I have spent a day without solving my problem. Maybe someone can help.

I have a query and the result from this query is to be used in other queries.

A schematic view of the result in the query looks like this.
Item Test
A 100
B -50
C 200

If the result is <0, it will affect my succeeding calculations in a bad way.
If I use the criteria >0, I only get the result for A and C, which spoils all
succeeding calculations.

I thought it would be possible to use an If Then criteria, which would say
something like “if the result is <0 then put in 0 insteadâ€. So, I tried
=IIf([Test]<0 Then 0)
The return is only one empty item row.

I sure would be very grateful if someone can give me an advice.
Catarina
 
Thanks.
But I'am not sure I really understand what # As NewTest From TableName #
means.
Catarina

"Ofer" skrev:
Try this iif

Select Item, iif([Test]<0,0,[Test]) As NewTest From TableName

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Foldur said:
Hello
I have spent a day without solving my problem. Maybe someone can help.

I have a query and the result from this query is to be used in other queries.

A schematic view of the result in the query looks like this.
Item Test
A 100
B -50
C 200

If the result is <0, it will affect my succeeding calculations in a bad way.
If I use the criteria >0, I only get the result for A and C, which spoils all
succeeding calculations.

I thought it would be possible to use an If Then criteria, which would say
something like “if the result is <0 then put in 0 insteadâ€. So, I tried
=IIf([Test]<0 Then 0)
The return is only one empty item row.

I sure would be very grateful if someone can give me an advice.
Catarina
Click to expand...
Click to expand...
 
In the query, when you define one of the fields as
NewTest: iif([Test]<0,0,[Test])

Then in the SQL it will look lik
iif([Test]<0,0,[Test]) As NewTest

In the criteria you need to define a new name to return, you can't use one
of the fields name in the table

----------
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Foldur said:
Thanks.
But I'am not sure I really understand what # As NewTest From TableName #
means.
Catarina

"Ofer" skrev:
Try this iif

Select Item, iif([Test]<0,0,[Test]) As NewTest From TableName

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Foldur said:
Hello
I have spent a day without solving my problem. Maybe someone can help.

I have a query and the result from this query is to be used in other queries.

A schematic view of the result in the query looks like this.
Item Test
A 100
B -50
C 200

If the result is <0, it will affect my succeeding calculations in a bad way.
If I use the criteria >0, I only get the result for A and C, which spoils all
succeeding calculations.

I thought it would be possible to use an If Then criteria, which would say
something like “if the result is <0 then put in 0 insteadâ€. So, I tried
=IIf([Test]<0 Then 0)
The return is only one empty item row.

I sure would be very grateful if someone can give me an advice.
Catarina
Click to expand...
Click to expand...
Click to expand...
 
Back
Top