grouping issue with ' is not null '

[trevorC via AccessMonster.com]

Hi All,
This first query all works fine, giving the expected results.
The second query is a copy of the first query and i changed the " IN " to "
OUT ", but this gives an error if the line ' HAVING (((gamrep.Out) Is Not
Null)) ' is missing. With this line in the query does work but the result is
no longer grouped as in the first query, the output is showing the entries
for each day for the week but not grouped as on entry and one total.

Both Queries show the first and last date of each week and a total Quantity

Can you help?

********First Query - Received Date *********************

SELECT
Format(DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in]),"dd mmm
yyyy") AS [Start of Week],
Format(DateAdd("d",-DatePart("w",[gamrep]![in],1)+6,[gamrep]![in]),"dd mmm
yyyy") AS [End of Week],
Count(*) AS [Received Qty]
FROM gamrep

GROUP BY
DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in]),
DateAdd("d",-DatePart("w",[gamrep]![in],1)+6,[gamrep]![in])

ORDER BY
DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,
[gamrep]![in]) DESC;

********Second Query - Dispatch Date *********************

SELECT
Format(DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,[gamrep]![out]),"dd mmm
yyyy") AS [Start of Week],
Format(DateAdd("d",-DatePart("w",[gamrep]![out],1)+6,[gamrep]![out]),"dd mmm
yyyy") AS [End of Week],
Count(*) AS [Completed Qty]
FROM gamrep

GROUP BY
DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,[gamrep]![out]),
DateAdd("d",-DatePart("w",[gamrep]![out],1)+6,[gamrep]![out]),
gamrep.Out

HAVING (((gamrep.Out) Is Not Null))
ORDER BY
DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,
[gamrep]![out]) DESC;

 

[Marshall Barton]

This first query all works fine, giving the expected results.
The second query is a copy of the first query and i changed the " IN " to "
OUT ", but this gives an error if the line ' HAVING (((gamrep.Out) Is Not
Null)) ' is missing. With this line in the query does work but the result is
no longer grouped as in the first query, the output is showing the entries
for each day for the week but not grouped as on entry and one total.

Both Queries show the first and last date of each week and a total Quantity

Can you help?

********First Query - Received Date *********************

SELECT
Format(DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in]),"dd mmm
yyyy") AS [Start of Week],
Format(DateAdd("d",-DatePart("w",[gamrep]![in],1)+6,[gamrep]![in]),"dd mmm
yyyy") AS [End of Week],
Count(*) AS [Received Qty]
FROM gamrep

GROUP BY
DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in]),
DateAdd("d",-DatePart("w",[gamrep]![in],1)+6,[gamrep]![in])

ORDER BY
DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,
[gamrep]![in]) DESC;

********Second Query - Dispatch Date *********************

SELECT
Format(DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,[gamrep]![out]),"dd mmm
yyyy") AS [Start of Week],
Format(DateAdd("d",-DatePart("w",[gamrep]![out],1)+6,[gamrep]![out]),"dd mmm
yyyy") AS [End of Week],
Count(*) AS [Completed Qty]
FROM gamrep

GROUP BY
DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,[gamrep]![out]),
DateAdd("d",-DatePart("w",[gamrep]![out],1)+6,[gamrep]![out]),
gamrep.Out

HAVING (((gamrep.Out) Is Not Null))
ORDER BY
DateAdd("d",-DatePart("w",[gamrep]![out],1)+2,
[gamrep]![out]) DESC;

Why are you still using the Format function?

That HAVING should be WHERE.