Condition in report? Access 2007

[Scorp Scorp]

Dear all
Am trying to put the following query in a report

SELECT color, sum(qty*u.p) as total 
FROM table 
WHERE flag = "00" 
GROUP BY color
 

couldnt set the fillter
i tried :

docmd.openreport "rpt", acViewPreview, wherecondition:= "flag = 00 group by color" 

i got missing operator errot, but have no idea what operator.

rpt is created in design on table table which is the one in the above query

Also i couldt find a wasy to place a textbox in rpt in order to hold the value SUM(qty*u.P)

Any ideass ???
regards,

[TheSmileyCoder]

Your WhereCondition includes your group by clause. Your where statement is:

WHERE flag="00"

and is seperate from your GROUP by clause:

GROUP BY color

If flag is a string type field, which it seems to be from your example, you need to include that part in your where condition by enclosing the 00 in single quotes:

docmd.openreport "rpt", acViewPreview, wherecondition:= "flag = '00'" 

As for the textbox, place a textbox in the report, and set its controlsource to Total since thats what you have specified it should be called in your query (see the sum(qty*u.p) as total )

[Scorp Scorp]

Very thanks for your fast reply man, but how can i group the result ??

Also , how can the textbox knows the total even if i assign the controlsource as total, since am not bounding it to that query (the query is an example to show the result i need in the report only), am bounding the report to the table table and am filtering the records upon my where condition only , then i need to get the totals of the filtered records??


I tried ur statement:
it generated the color without grouping where the condition is true
and it calculated the SUM(qty*u.p) on the whole table that has the condition true, and assign that sum to each record
Which means am having unique sum for all records, and records are not distinct or grouped .

[TheSmileyCoder]

With the report open in design view, you can specify a group by. I dont know exactly where this setting is in Ac2007, sorry. Once you have found it, you can also specify that each group should have a group footer. In the group footer you should be able to add a textbox with controlsource:=sum(qty*u.p)

[Scorp Scorp]

ill check it , then be back with a post.

[Scorp Scorp]

Hello thesmileycoder

i edited the filter by adding the '' , worked fine.
but when i tried to pass the value in a string like:

docmd.openreport "rpt", acViewPreview, wherecondition:= "flag = ' " & strvalue & " ' AND other = ' " & strvalue2 & " ' " 

Where other is another colomn criteria The report opens but with no records even when i switch to design mode after i run the report, am checking the Filter property, and it says
flag='00' AND other = 'vx' but as if its not accepting the values since no records showing.

What am i doing wrong ???

Regarding the bound issue, switch to layout mode, click on Group & Sort under Grouping and totals a new bar will show at the bottom of the report, click on Add a group, and select the feild u want to group with, very easy :)

[TheSmileyCoder]

Just to make sure, if you open the report without adding the filter, you DO see your records, right?

Are any of the fields in your recordset lookup columns made with the lookup wizard? (which I recommend against btw)

Other then that I dont have any good suggestions, try posting your reports recordsource SQL.

[Scorp Scorp]

Well , it seem that the filter wont work if you pass string values , rather than you type the value ie: passing str1 of value 00 wont work
while typing-in the value '00' in the filter property works fine.
Since i created another feild of type number, and i passed multi values(integer values) to the filter and then yoopppiiiiiii , it worked fine :)

but i still am not sure/ or decide if passing string variable wont work, need a little deeper diggggiiinnn .

Numbers worked fine.
Thanks for your help SmileyC. really appriciate.