If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
grouping issue with ' is not null '
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/For...eries/201005/1 |
#2
|
|||
|
|||
grouping issue with ' is not null '
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] |
Thread Tools | |
Display Modes | |
|
|