IIf Statement Error

[Mary O]

I'm trying to formulate an if/then statement in a query and it returns with
error as the value.

I've a calculated expression as Expr2:

Expr2: First(([SumOfACREAGE]*10)*([% PMT]))

which returns a value. This part works well. It returns a dollar value.

I then use that Expr2 in another query in an IIf statement:

Expr7: IIf([Expr2]<"1000","Expr2","1000")

I want it to return a value of 1000 if the value of Expr2 is greater than
1000, and Expr2 if the value is less than 1000. It's not working. I've
tried adding .00 to the 1000 because Expr2 is a dollar value, but neither
works.

Can anyone help please?

Thanks.

 

[Jeff Boyce]

Mary

It looks like you're treating "Expr2" as a numeric value in the first query,
but as a text value in the second... why?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Mary O]

Thanks for replying, Jeff.

The second value should be a number, however in the property sheet there are
no values in the format available to select, like there typically are.

Any ideas how to proceed?

--
MAO

Mary

It looks like you're treating "Expr2" as a numeric value in the first query,
but as a text value in the second... why?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'm trying to formulate an if/then statement in a query and it returns
with
error as the value.

I've a calculated expression as Expr2:

Expr2: First(([SumOfACREAGE]*10)*([% PMT]))

which returns a value. This part works well. It returns a dollar value.

I then use that Expr2 in another query in an IIf statement:

Expr7: IIf([Expr2]<"1000","Expr2","1000")

I want it to return a value of 1000 if the value of Expr2 is greater than
1000, and Expr2 if the value is less than 1000. It's not working. I've
tried adding .00 to the 1000 because Expr2 is a dollar value, but neither
works.

Can anyone help please?

Thanks.

.

 

[John Spencer]

Usually, I just recalculate the value. Sometimes Access can use the alias for
the calculation.

IIf(First(([SumOfACREAGE]*10)*([% PMT]))<1000,First(([SumOfACREAGE]*10)*([%
PMT])),1000)

You can try removing the quote marks.
IIf([Expr2]<1000,Expr2,1000)

By the way "It's not working" is a bad way to tell us about the problem. You
should try to be more specific and tell us exactly what is happening.
It returns an error message (what error message)
It returns no values
It returns the wrong values (what is wrong with what it returns)
The query freezes and never runs
My computer starts smoking and prints outs phrases like "Hey Dude".


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County