Comparing two fields in a query?

[jason2885]

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.

 

[Brianwarnock]

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

 

[jason2885]

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

 

[Brianwarnock]

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

 

[jason2885]

It is saying that there is a closing paranthesis, bracket or bar missing?

 

[boblarson]

Add one ) at the very end.

 

[jason2885]

When I put an extra paranthesis it says the expression you have entered has the wrong number of arguments

 

[boblarson]

Sorry, I missed that there are TWO missing ).

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

 

[Brianwarnock]

Correction

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

Brian

I see Bob corrected it whist I was typing

 

[jason2885]

Thanks that worked great.

 

[jason2885]

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.

 

[Brianwarnock]

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

 

[jason2885]

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.

 

[Brianwarnock]

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

 

[Brianwarnock]

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

Brian

 

[jason2885]

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.

 

[Brianwarnock]

Did you try Groupby on Things and Mileage with max on the IIF expression field?

Brian

 

[jason2885]

How do you do the groupby thing and the max are they functions somewhere.

 

[Brianwarnock]

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

 

[boblarson]

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.