Help with comparisons

[Imconfused]

I am trying to build a query that will compare values in 2 different fields then display the greater value. Any ideas?

 

[lespaul]

Case statement maybe? more details of what you are trying to accomplish will help determine the best way to proceed.

leslie

 

[Imconfused]

The table I am querying is very large (approx 38,000 records). Many of the fields contain certain values (dollars). Some of the fileds are reporting values that should match the value in a separate filed (within the same record). For reporting purposes I need to use the value in field "x" if it is equal to or greater than the corresponding value in field "y". If the corresponding value in field "y" is geater than the value in field "x", then I would want to use the "y" value. I want the query to do this for me. Please forgive me, everything I know about Access I have had to learn on my own.

 

[lespaul]

Try this:

iif(Field x >= field y, field x, field y)

 

[Imconfused]

Well, the query accepted the expression, however, no values are being displayed (I did check the "show" box). Below is the expression with the actual field names. BTW, thanks for your time.

IIf([Inducted Dollars]>=[JOQ Dollars],[Inducted Dollars],[JOQ Dollars])

 

[Imconfused]

I take that back, it did work except where there was a null value in some of the records (in one field). For the first several hundred records, the JOQ Dollars was null.

 

[lespaul]

I may have misunderstood your original post, (sorry!) try this:


iif(field x >= field y, fieldx, iif(field y > field x, field y, ''))

 

[GoDawgs]

IIf([Inducted Dollars]>=nz([JOQ Dollars]),[Inducted Dollars],[JOQ Dollars])

That should work...that'll turn the null JOQ dollars into 0's so the db can compare them. Hope the helps.

Kevin

 

[Imconfused]

Thanks, I finally got it to work using a combination of all of the suggestions. I had to use the Nz function in the JOQ Dollars field to convert the nulls into "0". Once that worked, everything else fell into place.