DSum with Criteria

  • Thread starter Thread starter niuginikiwi
  • Start date Start date
N

niuginikiwi

I have a Field on my report which has the following as its control source:

=Nz(DSum("PackQty","qryPacksActivity1","PackId=" & [PackId] & " And
[OrderDate] Between #" & [Forms!frmPackCrit!txtStartDate] & "# And #" &
[Forms!frmPackCrit!txtEndDate ] & "#"),0)

Basically its trying to get the sum of PackQty which is a number field in
query qryPacksAcivity1 which should filter by PackID and startDate and
EndDate range supplied from frmPackCrit

Instead I am getting a #Name? at runtime for that control source.

There a two tables behind qryPacksActivity1. They are:
tblOrders which as field OrderDate that is being used as the date range
criteria and tblOrderDetails which has the PackID field which is being used
as another criteria.

What am I doing wrong here?
 
Several things could go wrong here, e.g.:

a) You have an extra trailing space inside the square brackets for
txtEndDate.

b) You need square brackets around each of the terms, not one set around
the whole Forms thingy.

c) If PackID is null, the criteria would result in:
Packid = AND [OrderDate] ...
which is clearly not going to work. So, use Nz() to handle this.

d) Same thing crops up if either text box is null. Again, use Nz() to handle
this.

e) Concatenating the date into the string won't give give reliable results
if the user's regional setttings are non-US. Therefore use Format() around
the dates, to force the US date format.

f) If the form is not open, every time the report calls it, this is not
going to work.

g) If the report has no data, referring to the controls like this will
error. Test its HasData property to solve this.

i) If there is no text box named PackId on the report, add one (hidden if
you wish.) There are cases where Access can't figure out the expression if
there's no control by that name. Same with OrderDate.

j) Run qryPacksActivity1, and check that the PackQty column displays
right-aligned (like a number), not left-aligned (like text.) It may not sum
correctly if JET believes it is non-numeric.

So, you will end up with something like this:

=IIf([Report].[HasData],
DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/1/1900#), "mm\/dd\/yyyy")
&
"# And #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/1/2999#), "mm\/dd\/yyyy")
&
"#)", Null)

You may need to adjust the brackets above. If you are still stuck, simplify
the expression (using only part of the criteria) until you get it working,
and build up from there.
 
Hi Allen,

Thank you for pointing out the pitfalls and the better ways to do this.

I am going through your recommendations now BUT I thought I shuld give you a
bigger picture of what I am trying to achieve here and you might be able to
point out some other simpler and easier way of achieving this.

I have tables with relationships as follows:
tblAcq----<tblAcqDetails>----tblPacks---<tblOrderDetails>---tblOrders

tblAcq and tblAcqDetails process incoming packs and tblOrders and
tblOrderDetails process outgoing details. tblPacks holds the packs product
records.

I want to supply the following criteria
through a form to produce a report. - a Date Range which will filter PODate
from tblAcq and
OrderDate from tblOrders. Another criteria will filter PackName in tblPacks.

So I get a report that looks something like this:

Between FromDate And ToDate
PackName UnitsIn UnitsOut UnitsOnHand
F47 50 20 30
B46 100 50 50

and so on...

my table design is just like your example of calculating stock on hand on
your website.

--
niuginikiwi
Nelson, New Zealand


Allen Browne said:
Several things could go wrong here, e.g.:

a) You have an extra trailing space inside the square brackets for
txtEndDate.

b) You need square brackets around each of the terms, not one set around
the whole Forms thingy.

c) If PackID is null, the criteria would result in:
Packid = AND [OrderDate] ...
which is clearly not going to work. So, use Nz() to handle this.

d) Same thing crops up if either text box is null. Again, use Nz() to handle
this.

e) Concatenating the date into the string won't give give reliable results
if the user's regional setttings are non-US. Therefore use Format() around
the dates, to force the US date format.

f) If the form is not open, every time the report calls it, this is not
going to work.

g) If the report has no data, referring to the controls like this will
error. Test its HasData property to solve this.

i) If there is no text box named PackId on the report, add one (hidden if
you wish.) There are cases where Access can't figure out the expression if
there's no control by that name. Same with OrderDate.

j) Run qryPacksActivity1, and check that the PackQty column displays
right-aligned (like a number), not left-aligned (like text.) It may not sum
correctly if JET believes it is non-numeric.

So, you will end up with something like this:

=IIf([Report].[HasData],
DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/1/1900#), "mm\/dd\/yyyy")
&
"# And #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/1/2999#), "mm\/dd\/yyyy")
&
"#)", Null)

You may need to adjust the brackets above. If you are still stuck, simplify
the expression (using only part of the criteria) until you get it working,
and build up from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
niuginikiwi said:
I have a Field on my report which has the following as its control source:

=Nz(DSum("PackQty","qryPacksActivity1","PackId=" & [PackId] & " And
[OrderDate] Between #" & [Forms!frmPackCrit!txtStartDate] & "# And #" &
[Forms!frmPackCrit!txtEndDate ] & "#"),0)

Basically its trying to get the sum of PackQty which is a number field in
query qryPacksAcivity1 which should filter by PackID and startDate and
EndDate range supplied from frmPackCrit

Instead I am getting a #Name? at runtime for that control source.

There a two tables behind qryPacksActivity1. They are:
tblOrders which as field OrderDate that is being used as the date range
criteria and tblOrderDetails which has the PackID field which is being
used
as another criteria.

What am I doing wrong here?
Click to expand...
Click to expand...
 
Hi Allen,

I have gone through the control source express you have provided me and did
some changes with the brackets and it appears to be working.

However, I still have an issue. That is, the report is based on tblPacks
and on the details section of the report there are two text boxes which I am
using DSum which one of them you have helped out on.

txtQtyReceived control source is :
=IIf([Report].[HasData],DSum("QtyIn","qryPacksActivity2","PackId=" &
Nz([PackId],0) & " AND [PODate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy") &
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy") &
"#"),Null)

and that works fine.

the other one is txtQtyUsed and its control source is :
=IIf([Report].[HasData],DSum("PackQty","qryPacksActivity1","PackId=" &
Nz([PackId],0) & " AND [qryPacksActivity1].[OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy") &
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy") &
"#"),Null)

this is where the issue is. txtQtyUsed does not return anything, not even an
error message.

what am i doing wrong?

--
niuginikiwi
Nelson, New Zealand


Allen Browne said:
Several things could go wrong here, e.g.:

a) You have an extra trailing space inside the square brackets for
txtEndDate.

b) You need square brackets around each of the terms, not one set around
the whole Forms thingy.

c) If PackID is null, the criteria would result in:
Packid = AND [OrderDate] ...
which is clearly not going to work. So, use Nz() to handle this.

d) Same thing crops up if either text box is null. Again, use Nz() to handle
this.

e) Concatenating the date into the string won't give give reliable results
if the user's regional setttings are non-US. Therefore use Format() around
the dates, to force the US date format.

f) If the form is not open, every time the report calls it, this is not
going to work.

g) If the report has no data, referring to the controls like this will
error. Test its HasData property to solve this.

i) If there is no text box named PackId on the report, add one (hidden if
you wish.) There are cases where Access can't figure out the expression if
there's no control by that name. Same with OrderDate.

j) Run qryPacksActivity1, and check that the PackQty column displays
right-aligned (like a number), not left-aligned (like text.) It may not sum
correctly if JET believes it is non-numeric.

So, you will end up with something like this:

=IIf([Report].[HasData],
DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/1/1900#), "mm\/dd\/yyyy")
&
"# And #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/1/2999#), "mm\/dd\/yyyy")
&
"#)", Null)

You may need to adjust the brackets above. If you are still stuck, simplify
the expression (using only part of the criteria) until you get it working,
and build up from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
niuginikiwi said:
I have a Field on my report which has the following as its control source:

=Nz(DSum("PackQty","qryPacksActivity1","PackId=" & [PackId] & " And
[OrderDate] Between #" & [Forms!frmPackCrit!txtStartDate] & "# And #" &
[Forms!frmPackCrit!txtEndDate ] & "#"),0)

Basically its trying to get the sum of PackQty which is a number field in
query qryPacksAcivity1 which should filter by PackID and startDate and
EndDate range supplied from frmPackCrit

Instead I am getting a #Name? at runtime for that control source.

There a two tables behind qryPacksActivity1. They are:
tblOrders which as field OrderDate that is being used as the date range
criteria and tblOrderDetails which has the PackID field which is being
used
as another criteria.

What am I doing wrong here?
Click to expand...
Click to expand...
 
1900 might be to early as a substitute for null on the ending date.

You don't need the query name in the string before OrderDate.

=IIf([Report].[HasData], DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/01/1900#),
"mm\/dd\/yyyy") & "# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/01/2999#),
"mm\/dd\/yyyy") & "#)"), Null)


Presumably OrderDate is a Date/Time field in your table, and so it
right-aligns when you view the data in qryPacksActivity1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
niuginikiwi said:
Hi Allen,

I have gone through the control source express you have provided me and
did
some changes with the brackets and it appears to be working.

However, I still have an issue. That is, the report is based on tblPacks
and on the details section of the report there are two text boxes which I
am
using DSum which one of them you have helped out on.

txtQtyReceived control source is :
=IIf([Report].[HasData],DSum("QtyIn","qryPacksActivity2","PackId=" &
Nz([PackId],0) & " AND [PODate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"#"),Null)

and that works fine.

the other one is txtQtyUsed and its control source is :
=IIf([Report].[HasData],DSum("PackQty","qryPacksActivity1","PackId=" &
Nz([PackId],0) & " AND [qryPacksActivity1].[OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"#"),Null)

this is where the issue is. txtQtyUsed does not return anything, not even
an
error message.

what am i doing wrong?

--
niuginikiwi
Nelson, New Zealand


Allen Browne said:
Several things could go wrong here, e.g.:

a) You have an extra trailing space inside the square brackets for
txtEndDate.

b) You need square brackets around each of the terms, not one set around
the whole Forms thingy.

c) If PackID is null, the criteria would result in:
Packid = AND [OrderDate] ...
which is clearly not going to work. So, use Nz() to handle this.

d) Same thing crops up if either text box is null. Again, use Nz() to
handle
this.

e) Concatenating the date into the string won't give give reliable
results
if the user's regional setttings are non-US. Therefore use Format()
around
the dates, to force the US date format.

f) If the form is not open, every time the report calls it, this is not
going to work.

g) If the report has no data, referring to the controls like this will
error. Test its HasData property to solve this.

i) If there is no text box named PackId on the report, add one (hidden if
you wish.) There are cases where Access can't figure out the expression
if
there's no control by that name. Same with OrderDate.

j) Run qryPacksActivity1, and check that the PackQty column displays
right-aligned (like a number), not left-aligned (like text.) It may not
sum
correctly if JET believes it is non-numeric.

So, you will end up with something like this:

=IIf([Report].[HasData],
DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/1/1900#),
"mm\/dd\/yyyy")
&
"# And #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/1/2999#),
"mm\/dd\/yyyy")
&
"#)", Null)

You may need to adjust the brackets above. If you are still stuck,
simplify
the expression (using only part of the criteria) until you get it
working,
and build up from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
niuginikiwi said:
I have a Field on my report which has the following as its control
source:

=Nz(DSum("PackQty","qryPacksActivity1","PackId=" & [PackId] & " And
[OrderDate] Between #" & [Forms!frmPackCrit!txtStartDate] & "# And #" &
[Forms!frmPackCrit!txtEndDate ] & "#"),0)

Basically its trying to get the sum of PackQty which is a number field
in
query qryPacksAcivity1 which should filter by PackID and startDate and
EndDate range supplied from frmPackCrit

Instead I am getting a #Name? at runtime for that control source.

There a two tables behind qryPacksActivity1. They are:
tblOrders which as field OrderDate that is being used as the date range
criteria and tblOrderDetails which has the PackID field which is being
used
as another criteria.

What am I doing wrong here?
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen,

Silly me. I had txtQtyUsed visible = No and that was why I could not see
anything.
Thanks for all your help. The report is now looking like the way I wanted.

The other thing I notice is that its taking a fraction of a second to load
up data onto the report. Is that because my DSum is getting data from a
query? Will that be a performance issue down the track with my FE/BE
environmnet on a LAN?

Thanks again.


--
niuginikiwi
Nelson, New Zealand


Allen Browne said:
1900 might be to early as a substitute for null on the ending date.

You don't need the query name in the string before OrderDate.

=IIf([Report].[HasData], DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/01/1900#),
"mm\/dd\/yyyy") & "# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/01/2999#),
"mm\/dd\/yyyy") & "#)"), Null)


Presumably OrderDate is a Date/Time field in your table, and so it
right-aligns when you view the data in qryPacksActivity1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
niuginikiwi said:
Hi Allen,

I have gone through the control source express you have provided me and
did
some changes with the brackets and it appears to be working.

However, I still have an issue. That is, the report is based on tblPacks
and on the details section of the report there are two text boxes which I
am
using DSum which one of them you have helped out on.

txtQtyReceived control source is :
=IIf([Report].[HasData],DSum("QtyIn","qryPacksActivity2","PackId=" &
Nz([PackId],0) & " AND [PODate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"#"),Null)

and that works fine.

the other one is txtQtyUsed and its control source is :
=IIf([Report].[HasData],DSum("PackQty","qryPacksActivity1","PackId=" &
Nz([PackId],0) & " AND [qryPacksActivity1].[OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"# AND #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate],#1/01/1900#),"mm\/dd\/yyyy")
&
"#"),Null)

this is where the issue is. txtQtyUsed does not return anything, not even
an
error message.

what am i doing wrong?

--
niuginikiwi
Nelson, New Zealand


Allen Browne said:
Several things could go wrong here, e.g.:

a) You have an extra trailing space inside the square brackets for
txtEndDate.

b) You need square brackets around each of the terms, not one set around
the whole Forms thingy.

c) If PackID is null, the criteria would result in:
Packid = AND [OrderDate] ...
which is clearly not going to work. So, use Nz() to handle this.

d) Same thing crops up if either text box is null. Again, use Nz() to
handle
this.

e) Concatenating the date into the string won't give give reliable
results
if the user's regional setttings are non-US. Therefore use Format()
around
the dates, to force the US date format.

f) If the form is not open, every time the report calls it, this is not
going to work.

g) If the report has no data, referring to the controls like this will
error. Test its HasData property to solve this.

i) If there is no text box named PackId on the report, add one (hidden if
you wish.) There are cases where Access can't figure out the expression
if
there's no control by that name. Same with OrderDate.

j) Run qryPacksActivity1, and check that the PackQty column displays
right-aligned (like a number), not left-aligned (like text.) It may not
sum
correctly if JET believes it is non-numeric.

So, you will end up with something like this:

=IIf([Report].[HasData],
DSum("PackQty","qryPacksActivity1",
"(PackId=" & Nz([PackId],0) & ") AND ([OrderDate] Between #" &
Format(Nz([Forms]![frmPackCrit]![txtStartDate], #1/1/1900#),
"mm\/dd\/yyyy")
&
"# And #" &
Format(Nz([Forms]![frmPackCrit]![txtEndDate], #1/1/2999#),
"mm\/dd\/yyyy")
&
"#)", Null)

You may need to adjust the brackets above. If you are still stuck,
simplify
the expression (using only part of the criteria) until you get it
working,
and build up from there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
I have a Field on my report which has the following as its control
source:

=Nz(DSum("PackQty","qryPacksActivity1","PackId=" & [PackId] & " And
[OrderDate] Between #" & [Forms!frmPackCrit!txtStartDate] & "# And #" &
[Forms!frmPackCrit!txtEndDate ] & "#"),0)

Basically its trying to get the sum of PackQty which is a number field
in
query qryPacksAcivity1 which should filter by PackID and startDate and
EndDate range supplied from frmPackCrit

Instead I am getting a #Name? at runtime for that control source.

There a two tables behind qryPacksActivity1. They are:
tblOrders which as field OrderDate that is being used as the date range
criteria and tblOrderDetails which has the PackID field which is being
used
as another criteria.

What am I doing wrong here?
Click to expand...
Click to expand...
Click to expand...
 
Yes: DSum() on every record will be a performance issue.

Some alternatives:
a) Create a totals query that groups by the desired fields.
Then outer-join it to your existing query, instead of the DSum().

b) Use a subquery.
 
Hi Allen,

Thank you for the pointers. I have spent the morning trying to come up witht
he query as you have pointed to replace my DSum but I am not able to get it
working.
Would you be able to give me an example SQL in the two options that you have
pointed out to me?

I have tables with relationships as follows:
tblAcq----<tblAcqDetails>----tblPacks---<tblOrderDetails>---tblOrders

tblAcq and tblAcqDetails process incoming packs where as tblOrders and
tblOrderDetails process outgoing packs and tblPacks hold packs records.

Thank you again
 
I have come up with this query with two subqueries that calculate the some of
QtyInwards and QtyOutwards as follows:

SELECT PackName,
(SELECT SUM(qtyIn)
FROM tblPacksAcqDetails
WHERE tblPacksAcqDetails.PackID = tblPacks.PackID) AS QtyInwards,
(SELECT SUM(PackQty)
FROM tblOrderDetails
WHERE tblOrderDetails.PackID = tblPacks.PackID)
AS QtyOutwards,
FROM tblPacks;

BUT, my problem is I need to filter the inwards with the PODate range which
is a field of tblPacksAcq
and filter the outwords with OrderDate (a date range) which is a filed from
tblOrders.

How can I get to solve this.
 
Back
Top