IF Less Then Criteria 2 fields

[weilerdo]

Hi All, I have a query that I just can't seem to get my head around. The end result is that I need the query to return ONLY records where the TOTAL_ON ( total on hand ) is equal to or less then the MINIMUM. part of the problem is that the MINIMUM field is a text so I have a field Min that converts it to a #. I cant seem to figure out how to do the WHERE clause to be

WHERE ([TOTAL_ON] > = [Min])

This is my current query:

SELECT INVEN.PART_DES, MASTER1.PART_NO, MASTER1.MINIMUM, Sum(INVEN.TOTAL_ON) AS SumOfTOTAL_ON, CLng([MINIMUM]) AS [Min]
FROM MASTER1 INNER JOIN INVEN ON MASTER1.PART_DES = INVEN.PART_DES
GROUP BY INVEN.PART_DES, MASTER1.PART_NO, MASTER1.MINIMUM;

 

[ByteMyzer]

SELECT INVEN.PART_DES, MASTER1.PART_NO, MASTER1.MINIMUM, Sum(INVEN.TOTAL_ON) AS SumOfTOTAL_ON, CLng([MINIMUM]) AS [Min]
FROM MASTER1 INNER JOIN INVEN ON MASTER1.PART_DES = INVEN.PART_DES
GROUP BY INVEN.PART_DES, MASTER1.PART_NO, MASTER1.MINIMUM
HAVING Sum(INVEN.TOTAL_ON) <= CLng(MASTER1.MINIMUM);

 

[weilerdo]

Thanks for the quick reply. That is giving me an error " Invalid use of Null " when I try to run it.

 

[ByteMyzer]

Try changing the HAVING clause to:

HAVING Sum(INVEN.TOTAL_ON) <= CLng(Nz(MASTER1.MINIMUM, 0))

 

[weilerdo]

That did it. Thanks a million on this one ByteMyzer