Select greater of 2 fields

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

Guest

How can I write a query that will select the greater of 2 fields in a row? For example
select empid, (the greater of pld1 or pld2), (the greater of flt1 or flt2)
Thanks!
 
You could use the IIf() function, as follows:

GreatestPLD: IIF(pld1 > pld2, pld1, pld2)

GreatestFLT: IIF(flt1 > flt2, flt1, flt2)

Insert each of the above calculated fields into the first row of a column in
your query (one column for each, of course). Insert the field, empid, in
the first row of another column.


hth,
 
This is working, but not correctly. Do the fields have to be number datatype? These fields are text. And thanks so much, I didn't know about this function (I'm teaching myself!)
 
Hi Denise,

When you said you wanted the 'greater of two fields', I assumed a number
datatype. But, that isn't necessarily a requirement. If your fields are
Text type but contain numbers which can be interpreted as a numeric value,
you can use the Val() function to get them to evaluate correctly. This is
because Access evaluates Text fields character-by-character while it
evaluates Number type fields on the numeric value. For example:

If pld1 = 23 and pld2 = 5 and the fields are Text type

GreatestPLD: IIF(pld1 > pld2, pld1, pld2) will show 5, the value of
pld2

using the Val() function as shown below will return 23, the value of pld1

GreatestPLD: IIF(Val(pld1) > Val(pld2), pld1, pld2)

If this does not resolve the problem, please post back with more details
about the data normally found in these fields and how you would determine
greater than values.


If you will post some more information about the data typically found in
your fields and how you would

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Denise said:
This is working, but not correctly. Do the fields have to be number
Click to expand...
datatype? These fields are text. And thanks so much, I didn't know about
this function (I'm teaching myself!)
 
I added the Val function and it works great except for some cases where the fields are blank. Is there a way to allow for blanks? Thanks again!
 
For that - to treat a null as a zero - there is the NZ() function:

GreatestPLD: IIF(NZ(Val(pld1)) > NZ(Val(pld2)), pld1, pld2)


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Denise said:
I added the Val function and it works great except for some cases where
Click to expand...
the fields are blank. Is there a way to allow for blanks? Thanks again!
 
I'm still getting a #ERROR result. This is my exact query, the fields pld1, pld2, flt1, and flt2 are now Number. Can you see anything else? I really appreciate the help with this. Thanks

SELECT EmpID, IIf(NZ(Val(pld1))>NZ(Val(pld2)),pld1,pld2), IIf(NZ(Val(flt1))>NZ(Val(flt2)),flt1,flt2), Hrsleftcme
FROM Ledger
ORDER BY EmpID
 
Now that the fields are Number Type, let's remove the Val() function. See
what this does:

IIf(NZ(pld1, 0)>NZ(pld2, 0),pld1,pld2), IIf(NZ(flt1, 0)>NZ(flt2,
0),flt1,flt2)


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Denise said:
I'm still getting a #ERROR result. This is my exact query, the fields
Click to expand...
pld1, pld2, flt1, and flt2 are now Number. Can you see anything else? I
really appreciate the help with this. Thanks!
SELECT EmpID, IIf(NZ(Val(pld1))>NZ(Val(pld2)),pld1,pld2),
Click to expand...
IIf(NZ(Val(flt1))>NZ(Val(flt2)),flt1,flt2), Hrsleftcme1
 
Thank you Cheryl, that worked perfectly! Do you have a recommendation for any books that coverAccess really well? I have one but it doesn't mention any of the functions you were able to tell me about, so I think I need a better one. Thanks again, Denise.
 
You're welcome!

As to books, I cannot think of one that details the usage of each/every
function. However, the book that I recommend most and believe belongs on
every programmer's bookshelf is the "Access xx Developer's Handbook" [xx =
2002, 2000, 97, etc.] by Ken Getz (and various co-authors, depending on
version). The book is for medium- to advanced-level programmers, but for
those who want to 'move up' and expand their knowledge, it is a must.

Also, you may not be aware that practically every question asked and
answered in these forums is archived on Google. For example, you could go
to http://groups.google.com and do a search on: recommend Access books
and get hundreds of responses that appeared right in these Access news
groups. You could note some of those which sound interesting and then go
to Amazon.com and search out availability/price and on some of them read
excerpts and reviews.

And, last, keep on using these Access news groups - that is what we are here
for! Good luck with your project.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Denise said:
Thank you Cheryl, that worked perfectly! Do you have a recommendation for
Click to expand...
any books that coverAccess really well? I have one but it doesn't mention
any of the functions you were able to tell me about, so I think I need a
better one. Thanks again, Denise.
 
Back
Top