MAX Question 1

[TCARPENTER]

I thought what I was trying to do was simple, but....

I have a table:

SSTools:
SSTool_ID - PK - Autonumber
SSTool_No - Index - Text (no duplicates)

Some data:

SSA-001
SSA-002R
SSA-002L
SSE-001R
SSE-001L
SSA-003A
SSE-002
...

I tried a simple Max query which worked:

SELECT Max(SSTools.SSTools_No) AS MaxOfSSTools_No
FROM SSTools
HAVING (((Max(SSTools.TL_SSTools_No)) Like "SSE*"));

This one however, did not:

SELECT Max(SSTools.SSTools_No) AS MaxOfSSTools_No
FROM SSTools
HAVING (((Max(SSTools.TL_SSTools_No)) Like "SSA*"));

It returned nothing. I'm trying to get the last or greatest number in the series to add another number on the form so the user won't have to remember the last one used. What gives?

TIA
Todd

 

[Golom]

The HAVING clause operates after the MAX has been determined and ... since the MAX is SSE-02, there are no records where MAX is LIKE 'SSA*'. You need to apply the filter to the individual records ... not to the MAX record.

SELECT Max(SSTools.SSTools_No) AS MaxOfSSTools_No
FROM SSTools
WHERE TL_SSTools_No Like 'SSA*';

 

[TCARPENTER]

Ooooohhhhh....

Duh - thanks Golom - have a star!

Todd