Comparing two fields in a query?

jason2885

Registered User.
Local time
Today, 14:37
Joined
Jun 18, 2007
Messages
99
I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.
 
Am I correct to assume that the 2 fields contain dates and you want the most recent of the 2?
If so
MostRecentDate:IIf([Date1]>[Date2],[Date1],[Date2])

Brian
 
That worked but if there is no date in one of the two fields I need it to display just the the other date for example. How would I be able to show the second field1 where instead of it being blank.

Field 1 Field 2 Most Recent
12/25/1989 8/9/2004 8/9/2004
12/25/1989 blank 12/25/1989
 
Last edited:
MostRecentDate:IIf(Isnull([Date2],[Date1],IIf([Date1]>[Date2],[Date1],[Date2])

Should do the trick as if date1 is null the 2nd IIF returns a False and thus displays Date2.

Brian
 
It is saying that there is a closing paranthesis, bracket or bar missing?
 
When I put an extra paranthesis it says the expression you have entered has the wrong number of arguments
 
Sorry, I missed that there are TWO missing ).

MostRecentDate:IIf(Isnull([Date2]),[Date1],IIf([Date1]>[Date2],[Date1],[Date2]))
 
Correction

MostRecentDate:IIf(Isnull([Date2]),[Date1],IIf([Date1]>[Date2],[Date1],[Date2]))

Brian

I see Bob corrected it whist I was typing
 
Ok well now that that is fixed I have one more slight problem with this query I have 1000s of different things that I got the most recent date for the only thing is it showing the name of each thing the number of times that there is a date in the system for it. Is there anyway to correct that particular problem.
 
I now assume that you only have 2 fields in the query, the IIf... field and the "things Name" field, using a Totals query Groupby Things and Max the IIF

Brian
 
Actually it is the name of the thing, how long it is, and then there are two different process that the last date of each have been selected. Would this effect your solution any.
 
Possibly, If all the other fields are always the same for each name I think that you can groupby on them all, but I suspect that this is not the case and that you want the name to appear several times with the variable data and the last date for all of those.
An example of required output might help, it could be that you are going to need multiple queries, perhaps you can zip and attach youdb, compact it first.

Brian
 
Actually I would try a groupBy on all the selected fields with the Max on the IIf.

Brian
 
Ok this is basically what I have at the moment

Things | Mileage| Date Process1 | Date Process2 |
12341201|5|
12341202|89|
12341202|89|
12341202|89|
12341203|34|
12341204|2|
12341205|45|
12341205|45|

What I need is to only be able to show each of the things once. I hope this makes it more clear what my problem is. The things and mileage are just coming off a database.
 
Did you try Groupby on Things and Mileage with max on the IIF expression field?

Brian
 
How do you do the groupby thing and the max are they functions somewhere.
 
They are functions of a Total type Query, when in query design click on the sigma icon, I'm sorry I don't know how to show that, and an extra line appears in the query grid, use the drop down to select the functions.

Brian
 
When editing a query in the QBE grid, look for this button and you will then see the Grouping (GROUPBY) appear and if you select the drop down on the words GROUPBY you will get the MAX function.

sigma.png
 

Users who are viewing this thread

Back
Top Bottom