grouping issue with ' is not null '
in [Queries]
|
Prev: Combobox Dropdown jump to selections starting with E's
Next: Updating values from the previous record
From: trevorC via AccessMonster.com on 4 May 2010 20:21 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; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: Marshall Barton on 5 May 2010 11:41
trevorC via AccessMonster.com wrote: >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. -- Marsh MVP [MS Access] |