Maximum of 2 fields

[macroapa]

Hi I have a table called 'Prep' with the following structure:

DateBookedL1 DateBookedL2
08/08/2008 14:32:53 10/08/2008 14:32:53
07/08/2008 14:32:53 06/08/2008 14:32:53

What I want to do is run a query that returns the greater of the 2
fields for each record so the results would look like:

MaxDate
10/08/2008 14:32:53
07/08/2008 14:32:53

I've done a search on google but cant get anything to work.

Any help appreciated.

Thanks.

 

[schasteen]

Just compare them with an if
=iif([DateBookedL1] > [DateBookedL2],[DateBookedL1],[DateBookedL2])

Not sure what you would want if they are equal, but just nest the if
statements to handle it.

 

[macroapa]

I've managed to come up with:

SELECT
Format((Prep.DateBookedL1+Prep.DateBookedL2+Abs(Prep.DateBookedL1-
Prep.DateBookedL2))/2,"dd/mm/yyyy hh:mm:ss") AS DateMax
FROM Prep;

and this works as long as there is a date in both fields, but not if
one field is blank.

 

[macroapa]

Many thanks, think i';ve got it sorted now with:

SELECT
iif(IsNull(DateBookedL1),DateBookedL2,iif(IsNull(DatebookedL2),DateBookedL1,iif([DateBookedL1]

[DateBookedL2],[DateBookedL1],[DateBookedL2]))) AS DateMax

FROM Prep;

 

[a a r o n . k e m p f]

in SQL Server you could just write this


Select ISNULL(DateBookedL1, DateBookedL2)
or even
Select COALESCE(DateBookedL1, DateBookedL2, GetDate())

 

[Please Learn to Read]

Please learn to read. The question was posted to an Access newsgroup and did
not ask "How could I do this in SQL Server?". The reasonable guess would have
been that the poster wanted an answer about Access, not about some other
product. We realize that illiteracy seems to be a problem that is affecting
your public image, and to prevent making a fool of yourself in public in the
future, we can offer the advice that training in reading English for
illiterates is provided at no or low cost by many local welfare and
educational organizations. Please sign up and learn to read.

2008 Learn to Read Campaign

 

[a a r o n . k e m p f]

there is no such thing as a database that shouldn't be moved to SQL
Server
there is no such thing as a database that shouldn't be moved to SQL
Server
there is no such thing as a database that shouldn't be moved to SQL
Server

sorry that you haven't seen the light

 

[George Hepworth]

IMHO, the best way to deal with trolls is a two-part strategy.



1. Ignore their standard rantings.

2. Step in when it is necessary to correct a misstatement that could
potentially mislead a novice poster.



While it is true that novice posters don't know a great deal about Access,
it is also true that most of them are smart enough to figure out quite
quickly, and on their own, who the trolls are.