Give the highest number in a field of a table (1 Viewer)

JYeoman

Registered User.
Local time
Tomorrow, 00:43
Joined
Aug 28, 2018
Messages
17
Hi,
I am writing SQL for a query in which I find the highest number in an autonumber. That in itself is very easy, however the users often create new records by mistake without filling them in.

This is a problem because the autonumber is referenced in another query which returns the rest of the values of a table based on whatever that autonumber is.

to combat that, I am adding in a WHERE code that needs to check if the row is empty first, but because the fields have currency format they default to $0, meaning I can not use IS NOT NULL.

This is what I have so far which didn't work:
Code:
SELECT MAX(AllData.[Week Number]) AS WeekNumber
FROM AllData
WHERE (AllData.[Total Group Sales]) IS NOT 0;
 

JYeoman

Registered User.
Local time
Tomorrow, 00:43
Joined
Aug 28, 2018
Messages
17
BTW the problem was with a typo in the table field name.
Following code works
Code:
SELECT Max(AllData.[Week Number]) AS WeekNumber
FROM AllData
WHERE NOT (AllData.[Total_Sales]) = 0;
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Feb 19, 2013
Messages
16,554
or you can use

WHERE AllData.[Total_Sales]<> 0;
 

Users who are viewing this thread

Top Bottom