Max function not working at all 1

[BrockLanders]

Hello
I have a very simple SQL query that is suppossed to return the latest date (most recent) from a table. The table is called tblTest and only contains one field called Date in the Date/Time data type. There are two records in the table: 11/16/2002 and 11/23/2002. When I run my query, it just gives me these two records rather than just the most recent date. Below is my SQL code.

SELECT tblTest.Date
FROM tblTest
GROUP BY tblTest.Date
HAVING (((tblTest.Date)=Max([tblTest].[Date])));

Any help on this issue would be much appreciated.
Thanks

 

[jebry]

Hi!

You need a subquery:

SELECT tblTest.Date
FROM tblTest
Where tblTest.Date = (Select Max([tblTest].[Date]) From tblTest)


hth
Jeff Bridgham
bridgham@purdue.edu

 

[MichaelRed]

... but ... but ... but ... why the subquery (as a subquery)? the subquery w/o the 'super' query would return the max, OR just add a where clause directly to the 'super' query ... but ... but ... then I MUST be missing something ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BrockLanders]

Thanks for the reply.
OK, now I would like to put in a Between statement. The query should only return values between the max date and say 11/20/02, which in my case would only return one record (11/23/02) because the other record in my table (11/16/02) is not in that range. Below is my SQL, but I am getting errors.

SELECT tblTest.Date
FROM tblTest
HAVING ((([tblTest].[Date]) Between Max([tblTest].[Date]) And #11/02/2002#))
ORDER BY [tblTest].[Date] DESC;

Any ideas?

 

[MichaelRed]

only that I perfer to not need to take 'baby steps' through the development of a process where the individual parts are well covered in the help and tutorial files which are part of the overall package installed.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[tlbroadbent]

This query returns the result you want and is much simpler. Neither the sub-query or the max(date) test is required. Also, I recommend using Where rather than Having unless performing aggregation.

SELECT tblTest.[Date]
FROM tblTest
WHERE [tblTest].[Date] >= #11/02/2002#
ORDER BY [tblTest].[Date] DESC;

I agree with MichaelRed about the subquery. It is unnecessary.

SELECT MaxDate=Max([Date]) FROM tblTest

However, if choosing more columns from the table based on the max date then the subquery is useful.

Select * From tblTest
Where [Date]=(SELECT Max([Date]) FROM tblTest) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BrockLanders]

OK tlbroadbent, how about if i do not want to hardcode a date. I would like to say something like:
Between Max([tblTest].[Date]) And Max([tblTest].[Date])-5)
with the -5 being the 5 days prior to the Max date.

Thanks

 

[RickSpr]

Use the DMax() function to retrieve the maximum date, and then do the arithmetic:

SELECT *
FROM tblTest
WHERE [Date] >= DMax("[Date]", "tblTest&quot - [Number of days]

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BrockLanders]

When I run the query, a box pops up with a 5 (because that is the number of days i specified to subtract) and I am suppossed to enter something. When I enter a date, the query returns nothing. However, when I enter just a number it seems to be working how it should.

How does it know to subtract 5 days when the Date field is formatted mm/dd/yyyy? Is there something I should do to strip just the dd out of the date?

Thanks

 

[RickSpr]

The way the date field is formatted doesn't matter, because formatting only affects how the date is displayed. The arithmetic is done on the stored form of the date.

Internally, a date is stored as a floating point number, where the integer part is the number of days since 12/30/1899. You can simply add and subtract an integer to this to do date arithmetic by number of days.

I used [Number of days] in the query in order to have it prompt you with an input box asking for "Number of days". I didn't intend it to ask for a date. If you will always want the last 5 days, simply replace "[Number of days]" with "5" in the query. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BrockLanders]

Cool, thanks!