Choosing 1 field over another in query

  • Thread starter Thread starter DCost
  • Start date Start date
D

DCost

Two of the fields in a query that I'm trying to run have
dollar amounts (Purchase Price & Appraised Value). How
would I run a query that picks the lower of these two
dollar amounts?
 
[email protected] said:
Two of the fields in a query that I'm trying to run have
dollar amounts (Purchase Price & Appraised Value). How
would I run a query that picks the lower of these two
dollar amounts?
Click to expand...

LowestDollarVal: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
[Appraised Value])
 
-----Original Message-----
"(e-mail address removed)"
Click to expand...
message news:[email protected]...
Two of the fields in a query that I'm trying to run have
dollar amounts (Purchase Price & Appraised Value). How
would I run a query that picks the lower of these two
dollar amounts?
Click to expand...

LowestDollarVal: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
[Appraised Value])
Click to expand...
That did work, however, it gave me both fields. What I
would like is just the lowest of the two fields to appear
in the query if possible. Thanks for your help.
 
[email protected] said:
-----Original Message-----
"(e-mail address removed)"
Click to expand...
message news:[email protected]...
Two of the fields in a query that I'm trying to run have
dollar amounts (Purchase Price & Appraised Value). How
would I run a query that picks the lower of these two
dollar amounts?
Click to expand...

LowestDollarVal: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
[Appraised Value])
Click to expand...
That did work, however, it gave me both fields. What I
would like is just the lowest of the two fields to appear
in the query if possible. Thanks for your help.
Click to expand...

That expression will return (in one column) whichever of the two fields is
lower. If you want to see both columns, but replace the value of the
higher one with a blank then you need two columns in the query with
expressions.

AppraisedValue: IIf([Appraised Value]<[Purchase Price], [Appraised Value],
Null)

PurchasePrice: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
Null)

You will have to modify to determine what you want to happen when the
prices are equal. The above will show both values in that case.
 
ex just have the Appraised value show on the ones that are
equal. (and the Purchase Price on the ones that the
purchase price is lower).
Click to expand...


AppraisedValue: IIf([Appraised Value]<=[Purchase Price], [Appraised Value],
Null)

PurchasePrice: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
Null)
 
-----Original Message-----
modify?
ex just have the Appraised value show on the ones that are
equal. (and the Purchase Price on the ones that the
purchase price is lower).
Click to expand...


AppraisedValue: IIf([Appraised Value]<=[Purchase Price], [Appraised Value],
Null)

PurchasePrice: IIf([Purchase Price]<[Appraised Value], [Purchase Price],
Null)



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.Hopefully, this is last e-mail, I really do appreciate
Click to expand...
you taking the time. The query is showing both fields,
purchase price & appraised value, I would like it to show
only the lower of the two.
Thanks again.
 
>.Hopefully, this is last e-mail, I really do
appreciate
you taking the time. The query is showing both fields,
purchase price & appraised value, I would like it to show
only the lower of the two.
Thanks again.
Click to expand...

I guess I don't understand the question. You can manipulate whether the
original value for the field is displayed or some replacement value (like
null) is displayed instead. Both fields (columns) will always be displayed
for every row. How could they not be?

Using this example data...

Purchase Price Appraised Value
10 15
12 9
5 8
6 6

....the expressions I gave you should yield...

Purchase Price Appraised Value
10 (null)
(null) 9
5 (null)
(null) 6

Please provide similar tables to indicate what you are getting as a result
and what you *want* as a result.
 
-----Original Message-----
>.Hopefully, this is last e- mail, I really do
appreciate

I guess I don't understand the question. You can manipulate whether the
original value for the field is displayed or some replacement value (like
null) is displayed instead. Both fields (columns) will always be displayed
for every row. How could they not be?

Using this example data...

Purchase Price Appraised Value
10 15
12 9
5 8
6 6

....the expressions I gave you should yield...

Purchase Price Appraised Value
10 (null)
(null) 9
5 (null)
(null) 6

Please provide similar tables to indicate what you are getting as a result
and what you *want* as a result.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.It's giving me
Click to expand...
Purchase Price Appraised Value
10 15
 
Back
Top