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:
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;