criteria issue

[will via AccessMonster.com]

Hello, i like to add more fields as a criteria to the strWhere...........i
like to [RequiredDate], [ID], and other fields...can anyone help with this. i
am stuck with this. thanks.....

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Desc" & Left$(strDescrip, lngLen)
End If
End If

 

[Michel Walsh]

add the conjunction, probably AND, and the criteria. Be sure to add space
around the keywords.


strWhere = strWhere & " AND otherIField=" & 444



Hoping it may help,
Vanderghast, Access MVP

 

[bigwillno2 via AccessMonster.com]

Hey Michel, sorry for my ignorance.........how can i apply that to what i
already have, everytime i try to apply something to it gives me an mistype
error.

and all i want to do is to set my strWhere condition variable to have
[OrderNo]-number type, [RequiredDate]-date type, [ID]-string, [ID_1]-string,
[Desc]-string type, [OrderQty]-number type
all the fields mentioned above are in my sched listbox.....and i want my
criteria to set to all of them.
i tried:

strWhere = "[OrderNo] = 11111" & Me.sched![OrderNo] _
& " AND [ID_1] = A331500M" & Me.sched![ID_1]

and that gives me a no no....any suggestion...with some details please...
thanks for your help again......this thing is really frustrating me.....sorry
about that

add the conjunction, probably AND, and the criteria. Be sure to add space
around the keywords.

strWhere = strWhere & " AND otherIField=" & 444

Hoping it may help,
Vanderghast, Access MVP

Hello, i like to add more fields as a criteria to the strWhere...........i
like to [RequiredDate], [ID], and other fields...can anyone help with

[quoted text clipped - 9 lines]

End If
End If

 

[Michel Walsh]

There are many problems. The first one is about what you want really... I
mean, if OrderNo = 11111, what is the intention behind:

strWhere = "[OrderNo] = 11111" & Me.sched![OrderNo]


I mean, the value to which OrderNo should be equal is ALREADY supplied, what
are you tryig to achieve with me.sched!orderNo ?


The second problem is, what is sched!orderNo ? is that is the form name
followed by the control name from which we have to get the value? If so,
try:

strWhere = "[OrderNo] = FROMS!sched![OrderNo] AND [ID_1] =
FORMS!sched![ID_1] "


Hoping it may help,
Vanderghast, Access MVP

Hey Michel, sorry for my ignorance.........how can i apply that to what i
already have, everytime i try to apply something to it gives me an mistype
error.

and all i want to do is to set my strWhere condition variable to have
[OrderNo]-number type, [RequiredDate]-date type, [ID]-string,
[ID_1]-string,
[Desc]-string type, [OrderQty]-number type
all the fields mentioned above are in my sched listbox.....and i want my
criteria to set to all of them.
i tried:

strWhere = "[OrderNo] = 11111" & Me.sched![OrderNo] _
& " AND [ID_1] = A331500M" & Me.sched![ID_1]

and that gives me a no no....any suggestion...with some details please...
thanks for your help again......this thing is really frustrating
me.....sorry
about that

add the conjunction, probably AND, and the criteria. Be sure to add space
around the keywords.

strWhere = strWhere & " AND otherIField=" & 444

Hoping it may help,
Vanderghast, Access MVP

Hello, i like to add more fields as a criteria to the
strWhere...........i
like to [RequiredDate], [ID], and other fields...can anyone help with

[quoted text clipped - 9 lines]

End If
End If

 

[bigwillno2 via AccessMonster.com]

Hey Michel,
sorry that i didnt explained myself better.

this items that were mentions before([OrderNo], [ID_1], [RequiredDate]) and
so on are columns in a listbox called SCHED, i did try what you posted before
and it still gives me no result.

the list box looks like this:
OrderNo|ID_1|RequiredDate|........
what i am looking for is to use all columns on the list as criteria. and
bellow is the current code that i have to set a criteria for my report.
thanks for your help again.....

If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","

'Build up the description from the text in the visible column.
See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)

 

[Michel Walsh]

Right now, strWhere should be, when printed

Debug.Print strWhere


something look like:


OrderNo IN( "aaa", "b", "cde")



So, build similar string for other fields, such as string strScheduleDate,
to get, once printed, something like



ScheduleDate IN( #01-01-2001#, #02-02-2002#)



and then,

DoCmd.OpenReport "rptProduction", acViewPreview, _
WhereCondition:=strWhere & " AND " & strScheduleDate,
OpenArgs:=strDescrip


to get two criteria based on a list.




Hoping it may help,
Vanderghast, Access MVP

 

[bigwillno2 via AccessMonster.com]

Hey Michel thanks for being patient......

your last post look very promising, only thing that i am getting an error,
it's asking me for the [ID_1] selected......

this is my code, can you see what's wrong with it....

strDelim = "" 'Delimiter appropriate to field type. .
strDoc = "rptProduction"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible column.

strModelNum = strModelNum & strDelim & .Column(3, varItem) &
strDelim & ", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"
End If
lngLen = Len(strModelNum) - 1
If lngLen > 0 Then
strModelNum = "[ID_1] IN (" & Left$(strModelNum, lngLen) & ")"
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & " AND
" & strModelNum
', OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & " AND
" & strModelNum
', OpenArgs:=strDescrip

Right now, strWhere should be, when printed

Debug.Print strWhere

something look like:

OrderNo IN( "aaa", "b", "cde")

So, build similar string for other fields, such as string strScheduleDate,
to get, once printed, something like

ScheduleDate IN( #01-01-2001#, #02-02-2002#)

and then,

DoCmd.OpenReport "rptProduction", acViewPreview, _
WhereCondition:=strWhere & " AND " & strScheduleDate,
OpenArgs:=strDescrip

to get two criteria based on a list.

Hoping it may help,
Vanderghast, Access MVP

Hey Michel,
sorry that i didnt explained myself better.

[quoted text clipped - 55 lines]

OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)

 

[Michel Walsh]

ID_1 is a field in the table, or an ALIAS to a computed expression? If it is
an alias to a computed expression, you have to use the computed expression,
not the alias. That is required because it generally happens that the WHERE
clause is evaluated BEFORE the SELECT clause itself, so the aliases do not
exists, yet, when the WHERE clause is evaluated, and keep or reject the
rows.



Vanderghast, Access MVP

Hey Michel thanks for being patient......

your last post look very promising, only thing that i am getting an error,
it's asking me for the [ID_1] selected......

this is my code, can you see what's wrong with it....

strDelim = "" 'Delimiter appropriate to field type. .
strDoc = "rptProduction"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column.

strModelNum = strModelNum & strDelim & .Column(3, varItem)
&
strDelim & ", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"
End If
lngLen = Len(strModelNum) - 1
If lngLen > 0 Then
strModelNum = "[ID_1] IN (" & Left$(strModelNum, lngLen) & ")"
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "
AND
" & strModelNum
', OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "
AND
" & strModelNum
', OpenArgs:=strDescrip

Right now, strWhere should be, when printed

Debug.Print strWhere

something look like:

OrderNo IN( "aaa", "b", "cde")

So, build similar string for other fields, such as string strScheduleDate,
to get, once printed, something like

ScheduleDate IN( #01-01-2001#, #02-02-2002#)

and then,

DoCmd.OpenReport "rptProduction", acViewPreview, _
WhereCondition:=strWhere & " AND " & strScheduleDate,
OpenArgs:=strDescrip

to get two criteria based on a list.

Hoping it may help,
Vanderghast, Access MVP

Hey Michel,
sorry that i didnt explained myself better.

[quoted text clipped - 55 lines]

OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)

 

[bigwillno2 via AccessMonster.com]

Hey Michel got it....work like suga...sweet ...thanks again.....
now i was wondering if you could help me with one other problem i have with
the same list box. i am using the multiselect listbox to select orders that
need to be produced. and at the same time create purchase orders for internal
purposes. my purchase order is called with the docmd.openreport, and have a
where condition as you already know. however, my report have only three items,
vendor, item and quantity and none are the criteria "strWhere". the only way
i could get the report open is if i include the strWhere criteria, in this
case OrderNo, in the rowsource of the report. it's there a way to go around
this, and do i explain myself clearly, or do you need further explanation and
more visuals.

thanks again, i appreciate your patience and help.

ID_1 is a field in the table, or an ALIAS to a computed expression? If it is
an alias to a computed expression, you have to use the computed expression,
not the alias. That is required because it generally happens that the WHERE
clause is evaluated BEFORE the SELECT clause itself, so the aliases do not
exists, yet, when the WHERE clause is evaluated, and keep or reject the
rows.

Vanderghast, Access MVP

Hey Michel thanks for being patient......

[quoted text clipped - 86 lines]

 

[Michel Walsh]

You mean the query on which is based your report has no "OrderNo" field?
Can't you include that field in the SELECT clause, even if you don't print
it anywhere in the report, so you can then use a whereCondition argument, in
DoCmd.OpenReport, which will imply the OrderNo.


Vanderghast, Access MVP

 

[bigwillno2 via AccessMonster.com]

THANKS FOR REPONDING....
THAT'S EXCACTLY WHAT I HAVE RIGHT NOW......THE PROBLEM WITH THAT IS THAT
SINCE I HAVE OrderNo in my select clause, what shows in my query are multiple
OrderNo with that is using the same items, so "items" are repeated and their
quantity needed. visual....

i have.......listbox......sched that has multiple column
OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2

for every model number there are items that need to be ordered from vendors.
Here is where the Purchase Order report is generated. as i select from the
list box above, i click a button that prints the Purchase Order Report for
every selection from the listbox.

Problem: example above shows two different OrderNo of the same ModelNumber,
therefore, multiple of the same items will comeup for order from vendors
since is a multiselect list box.

i do get the items amounts right on the Purchase Order Report, the only thing
that i am having problem with is that same items are repeating. i do have a
test query that doesnt repeate the items, however it doesnt include OrderNo.
if i include OrderNo, the items will repeate since there could be multiple of
the same OrderNo. and if dont include this OrderNo on my test query, since
it's the strWhere clause criteria, i get no result on my report that has row
source test query that has no OrderNo field. sorry even explaining this is
difficult for me at this moment i hope you undertand.
this are the two query that i have......

1.....includes OrderNo, prints the report, but contains repeating items...
since there could be multiple order of the same ModelNum

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type, qrAllItems.
qFill, Sum(qrAllItems.OrderQty) AS CountOfqFill
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type, qrAllItems.
qFill
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));


2......does not Include OrderNo, contains the right items and the right
quantity, but doesnt have OrderNo, therefore, doesnt print the report since
the strWhere is OrderNo.....

SELECT [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill, Sum(
[qrAllItemsCountFX-Fab2].CountOfqFill) AS Total
FROM [qrAllItemsCountFX-Fab2]
GROUP BY [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill,
[qrAllItemsCountFX-Fab2].Type
WITH OWNERACCESS OPTION;


i hope you got an answer for this...thanks again..

You mean the query on which is based your report has no "OrderNo" field?
Can't you include that field in the SELECT clause, even if you don't print
it anywhere in the report, so you can then use a whereCondition argument, in
DoCmd.OpenReport, which will imply the OrderNo.

Vanderghast, Access MVP

Hey Michel got it....work like suga...sweet ...thanks again.....
now i was wondering if you could help me with one other problem i have

[quoted text clipped - 15 lines]

and
more visuals.

 

[Michel Walsh]

Well, the problem is then in the query itself, and it seems it should be
more secure to deal with it at the source. And once that is done, hopefully,
you will get the OrderNo field available in order to be able to filter it on
your report, report then based on the modified query.


Vanderghast, Access MVP

THANKS FOR REPONDING....
THAT'S EXCACTLY WHAT I HAVE RIGHT NOW......THE PROBLEM WITH THAT IS THAT
SINCE I HAVE OrderNo in my select clause, what shows in my query are
multiple
OrderNo with that is using the same items, so "items" are repeated and
their
quantity needed. visual....

i have.......listbox......sched that has multiple column
OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2

for every model number there are items that need to be ordered from
vendors.
Here is where the Purchase Order report is generated. as i select from the
list box above, i click a button that prints the Purchase Order Report for
every selection from the listbox.

Problem: example above shows two different OrderNo of the same
ModelNumber,
therefore, multiple of the same items will comeup for order from vendors
since is a multiselect list box.

i do get the items amounts right on the Purchase Order Report, the only
thing
that i am having problem with is that same items are repeating. i do have
a
test query that doesnt repeate the items, however it doesnt include
OrderNo.
if i include OrderNo, the items will repeate since there could be multiple
of
the same OrderNo. and if dont include this OrderNo on my test query, since
it's the strWhere clause criteria, i get no result on my report that has
row
source test query that has no OrderNo field. sorry even explaining this
is
difficult for me at this moment i hope you undertand.
this are the two query that i have......

1.....includes OrderNo, prints the report, but contains repeating items...
since there could be multiple order of the same ModelNum

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type, qrAllItems.
qFill, Sum(qrAllItems.OrderQty) AS CountOfqFill
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.Type,
qrAllItems.
qFill
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));


2......does not Include OrderNo, contains the right items and the right
quantity, but doesnt have OrderNo, therefore, doesnt print the report
since
the strWhere is OrderNo.....

SELECT [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill,
Sum(
[qrAllItemsCountFX-Fab2].CountOfqFill) AS Total
FROM [qrAllItemsCountFX-Fab2]
GROUP BY [qrAllItemsCountFX-Fab2].Vendor, [qrAllItemsCountFX-Fab2].qFill,
[qrAllItemsCountFX-Fab2].Type
WITH OWNERACCESS OPTION;


i hope you got an answer for this...thanks again..

You mean the query on which is based your report has no "OrderNo" field?
Can't you include that field in the SELECT clause, even if you don't print
it anywhere in the report, so you can then use a whereCondition argument,
in
DoCmd.OpenReport, which will imply the OrderNo.

Vanderghast, Access MVP

Hey Michel got it....work like suga...sweet ...thanks again.....
now i was wondering if you could help me with one other problem i have

[quoted text clipped - 15 lines]

and
more visuals.

 

[bigwillno2 via AccessMonster.com]

Hi Michel,

thanks for the help....yes, it must be the query, i dont find the way around
it.

this is my current query, it has OrderNo in it and strWhere picks this up for
the report. if i didnt have it there then my report will not display the data.
.....thanks for your help again...do you have any other suggestion to the
buttom of this.

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.qFill, Sum
(qrAllItems.OrderQty) AS CountOfqFill
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.qFill
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

Well, the problem is then in the query itself, and it seems it should be
more secure to deal with it at the source. And once that is done, hopefully,
you will get the OrderNo field available in order to be able to filter it on
your report, report then based on the modified query.

Vanderghast, Access MVP

THANKS FOR REPONDING....
THAT'S EXCACTLY WHAT I HAVE RIGHT NOW......THE PROBLEM WITH THAT IS THAT

[quoted text clipped - 75 lines]

 

[Michel Walsh]

Your second previous post list

OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2


So, I would add a WhereCondition on that data, and, in the report, makes the
GROUP and display the SUM in the group footer sections, with a text box
control source assigned to the expression like =SUM(OrderQty)

In other words, DO NOT USE the GROUP BY query, for your report, but the
whole table. The WhereCondition would remove the unwanted OrderNo, and the
GROUP-SUM mechanic will be taken care by the report generator, not by a
query.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

thanks for the help....yes, it must be the query, i dont find the way
around
it.

this is my current query, it has OrderNo in it and strWhere picks this up
for
the report. if i didnt have it there then my report will not display the
data.
....thanks for your help again...do you have any other suggestion to the
buttom of this.

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.qFill, Sum
(qrAllItems.OrderQty) AS CountOfqFill
FROM qrAllItems
GROUP BY qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.qFill
HAVING (((qrAllItems.Vendor)="Foamex-Fab"));

Well, the problem is then in the query itself, and it seems it should be
more secure to deal with it at the source. And once that is done,
hopefully,
you will get the OrderNo field available in order to be able to filter it
on
your report, report then based on the modified query.

Vanderghast, Access MVP

THANKS FOR REPONDING....
THAT'S EXCACTLY WHAT I HAVE RIGHT NOW......THE PROBLEM WITH THAT IS THAT

[quoted text clipped - 75 lines]

and
more visuals.

 

[bigwillno2 via AccessMonster.com]

Hey Michel,
you have been amazing with me, thanks. when you say add WhereCondittion,
which data....all of the columns or just OrderNo, as i have it now. And, when
you say the group footer, it's that the Detail section, because i only see,
report header, page header, Detail, report footer, page footer....sorry for
my ignorance, i really need help with this ...and thanks again.

Your second previous post list

OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2

So, I would add a WhereCondition on that data, and, in the report, makes the
GROUP and display the SUM in the group footer sections, with a text box
control source assigned to the expression like =SUM(OrderQty)

In other words, DO NOT USE the GROUP BY query, for your report, but the
whole table. The WhereCondition would remove the unwanted OrderNo, and the
GROUP-SUM mechanic will be taken care by the report generator, not by a
query.

Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

[quoted text clipped - 29 lines]

 

[Michel Walsh]

On the toolbar (of report design) you have a button called "Sorting and
Grouping", click it to get the Group 'sheet' where you can define the
groups. I assume you want to group on 'Required Date" field. Once you
defined a group, take note of properties at the bottom of the "Group Sheet",
check to see the group footer. THAT would make a new section visible in the
designer. In THAT section of the report, the group footer, add the required
text boxes, bind them to the fields, except for OrderQty for which you will
define a text box, BUT you will bind the control source NOT to the field,
but to the computed expression:

= SUM(OrderQty)


That control BEING in the GROUP footer, the SUM will occur over all FILTERED
records that belong to THAT group.



Alternatively, you can try to generate the report with the help of the
wizard.




Hoping it may help,
Vanderghast, Access MVP

Hey Michel,
you have been amazing with me, thanks. when you say add WhereCondittion,
which data....all of the columns or just OrderNo, as i have it now. And,
when
you say the group footer, it's that the Detail section, because i only
see,
report header, page header, Detail, report footer, page footer....sorry
for
my ignorance, i really need help with this ...and thanks again.

Your second previous post list

OrderNo|RequiredDate|ModelNumer|Description|OrderQty
46001 05/04/07 a33m any 1
40006 05/04/07 a33m any 2

So, I would add a WhereCondition on that data, and, in the report, makes
the
GROUP and display the SUM in the group footer sections, with a text box
control source assigned to the expression like =SUM(OrderQty)

In other words, DO NOT USE the GROUP BY query, for your report, but the
whole table. The WhereCondition would remove the unwanted OrderNo, and the
GROUP-SUM mechanic will be taken care by the report generator, not by a
query.

Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

[quoted text clipped - 29 lines]

and
more visuals.

 

[bigwillno2 via AccessMonster.com]

Thanks very much, i think i got it.......
only that i have limited option for the grouping
i only have the following...

SELECT qrAllItems.Vendor, qrAllItems.OrderNo, qrAllItems.qFill, qrAllItems.
OrderQty

i can't group by vendor, because it's already sorted by one vendor from the
query. By OrderNo, what do you think.........thanks again....it seems to be
working. qfill are the items themself...can i group it by those, what do you
think.....? thanks again.

On the toolbar (of report design) you have a button called "Sorting and
Grouping", click it to get the Group 'sheet' where you can define the
groups. I assume you want to group on 'Required Date" field. Once you
defined a group, take note of properties at the bottom of the "Group Sheet",
check to see the group footer. THAT would make a new section visible in the
designer. In THAT section of the report, the group footer, add the required
text boxes, bind them to the fields, except for OrderQty for which you will
define a text box, BUT you will bind the control source NOT to the field,
but to the computed expression:

= SUM(OrderQty)

That control BEING in the GROUP footer, the SUM will occur over all FILTERED
records that belong to THAT group.

Alternatively, you can try to generate the report with the help of the
wizard.

Hoping it may help,
Vanderghast, Access MVP

Hey Michel,
you have been amazing with me, thanks. when you say add WhereCondittion,

[quoted text clipped - 30 lines]

 

[Michel Walsh]

The ordering defined in the query WON'T be considered in the report unless
you do it specifically (same properties sheet, "SORTING and grouping"). So,
you can REDEFINE the grouping on fields that exist, in the query, even if
they are not sorted in the query. You can have MORE than ONE group, in the
report, and can activate the group-sections, as you wish, again, through the
"Sorting and Grouping" properties sheet available for the report.


Vanderghast, Access MVP

 

[bigwillno2 via AccessMonster.com]

Hey Michel,
thanks for your help, you have been soooo patient with me and love that....
thanks again....
one more question....why the result come with different spacing between them.

e.g...

result1

result2




result3
result4

thanks again for your help...you the best

The ordering defined in the query WON'T be considered in the report unless
you do it specifically (same properties sheet, "SORTING and grouping"). So,
you can REDEFINE the grouping on fields that exist, in the query, even if
they are not sorted in the query. You can have MORE than ONE group, in the
report, and can activate the group-sections, as you wish, again, through the
"Sorting and Grouping" properties sheet available for the report.

Vanderghast, Access MVP

Thanks very much, i think i got it.......
only that i have limited option for the grouping

[quoted text clipped - 11 lines]

you
think.....? thanks again.

 

[bigwillno2 via AccessMonster.com]

one last thing....how can add all this numbers.......i know it's a =sum() at
the report footer, but what expression, this =sum(OrderQty) has no name and
when i give it a name, it gives me and error when i try to add it..in the
report footer.....thanks again.

Hey Michel,
thanks for your help, you have been soooo patient with me and love that....
thanks again....
one more question....why the result come with different spacing between them.

e.g...

result1

result2

result3
result4

thanks again for your help...you the best

The ordering defined in the query WON'T be considered in the report unless
you do it specifically (same properties sheet, "SORTING and grouping"). So,

[quoted text clipped - 10 lines]