Access 2007 NZ() is working but the group by is not grouping

[chromarog]

I'm having trouble with the NZ() functionality. It works the way it should, I'm converting a null result to the number 3, but then my group by's are still seperating the results.

SELECT T_VSMSMP2_BreakDown.WOTYPE, [red]nz([T_VSMSMP2_BreakDown.PRIORITY],3) as PRIORITY[/red], Sum(T_VSMSMP2_BreakDown.REGHRS) AS SumOfREGHRS, Sum(T_VSMSMP2_BreakDown.OTHRS) AS SumOfOTHRS, AcctCalendar.AccMonth
FROM AcctCalendar, T_VSMSMP2_BreakDown
WHERE (((T_VSMSMP2_BreakDown.COMPLETIONDATE) Between [AcctCalendar].[StartDay] And [AcctCalendar].[EndDay]))
GROUP BY T_VSMSMP2_BreakDown.WOTYPE, T_VSMSMP2_BreakDown.PRIORITY, AcctCalendar.AccMonth
ORDER BY AcctCalendar.AccMonth;

results before, without the NZ():

WOTYPE PRIORITY SumOfREGHRS SumOfOTHRS AccMonth
BR 7 January
BR 1 16 January
BR 2 280.05 5.5 January
BR 3 64.25 January
SU 13 January
SU 2 24.75 January
SU 3 15.25 January


results after with the NZ():

WOTYPE PRIORITY SumOfREGHRS SumOfOTHRS AccMonth
BR 3 7 January
BR 1 16 January
BR 2 280.05 5.5 January
BR 3 64.25 January
SU 3 13 January
SU 2 24.75 January
SU 3 15.25 January
(I can't get the results to post right but I hope you get the idea)

The proirity columns are not summing up as I had hoped they would. I've looked into forcing the column to a numeric value but I'm not finding that bit of code. I've went back to the tables and they are all set to number types and the original table it was pulled from on the sql server side was a float data type. If I use an IsNull I get a "wrong number of arguments used with function in query expression" error. What am I doing wrong here? Or at least point me in the right direction or resource. I've been searching for the solution for a couple of hours now.

 

[PHV]

GROUP BY T_VSMSMP2_BreakDown.WOTYPE, nz([T_VSMSMP2_BreakDown.PRIORITY],3), AcctCalendar.AccM

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
GROUP BY T_VSMSMP2_BreakDown.WOTYPE, nz([T_VSMSMP2_BreakDown.PRIORITY],3), AcctCalendar.AccMonth

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Just as a cross reference, this question was originally posted here:

http://www.tek-tips.com/viewthread.cfm?qid=1598560

chromarog,

SkipVought also posted a solution there in the original thread, so be sure to take a look there as a cross reference.