DSum problem

[Fysh]

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and populate the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories could
be null altogether. Here is my code I have several options, but none seem to
work. How do I eliminate the nulls, in other words I would like to sum all
the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Ron Weiner]

Fysh Try:

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And Not (PointsScored1) Is Null")

Because Nulls are by definition undefined values you can not use any of the
normal comparison operators.

Ron W

 

[Fysh]

Thanks for the response, but it still does not work. It gives me the same
response: Run-time error 94; Invalid use of Null. I have tried several
variations but none seem to work.

Fysh Try:

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And Not (PointsScored1) Is Null")

Because Nulls are by definition undefined values you can not use any of the
normal comparison operators.

Ron W

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and populate the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories could
be null altogether. Here is my code I have several options, but none seem to
work. How do I eliminate the nulls, in other words I would like to sum all
the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Jeff Boyce]

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert Nulls to
....?

 

[Ron Weiner]

Fysh

Sorry that did not make your problem go away. In looking at your code I do
not see an obvious problem.

Now I am afraid we are going to need more information about your table
tblPracticalResults and your data in that table. Please post the DDL for
the table and provide some sample data and the values for PracticalIDNumber
and PractCATID that produce the error. I wouldn't be surprised that you
will be able to solve the problem once you take this systematic approach to
debugging.

Ron W

Thanks for the response, but it still does not work. It gives me the same
response: Run-time error 94; Invalid use of Null. I have tried several
variations but none seem to work.

Fysh Try:

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And Not (PointsScored1) Is Null")

Because Nulls are by definition undefined values you can not use any of the
normal comparison operators.

Ron W

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and

populate
the

unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories could
be null altogether. Here is my code I have several options, but none

seem
to

work. How do I eliminate the nulls, in other words I would like to

sum
all

the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" &

False &
"

And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Fysh]

Sorry I didn't back to you right away. The PracCATID and the
PracticalIDNUmber are both autonumber. PracticalIDNmber is placed into a
hidden textbox on the main form when I select a name from the combo box.
After this is updated I have a call function performed to update the fields
on the unbound subform. I have 20 rows of items on this subform each are
populated using a for loop. One of the items is the DSum in which I am
trying to create here. I did think of using a query, but I didn't want to
open and close the query for each item. Also, I figured the DSum would be
better in the loop.

The tblPracticalResults has about 100 rows for each test being perform for
an individual. This table shows the items being tested and scores. The
PracCATID is the ID of each section and the PracticalIDNumber is the ID
assigned in another table based against the student, type of test, location
of test, and other information.

Fysh

Sorry that did not make your problem go away. In looking at your code I do
not see an obvious problem.

Now I am afraid we are going to need more information about your table
tblPracticalResults and your data in that table. Please post the DDL for
the table and provide some sample data and the values for PracticalIDNumber
and PractCATID that produce the error. I wouldn't be surprised that you
will be able to solve the problem once you take this systematic approach to
debugging.

Ron W

Thanks for the response, but it still does not work. It gives me the same
response: Run-time error 94; Invalid use of Null. I have tried several
variations but none seem to work.

Fysh Try:

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And Not (PointsScored1) Is Null")

Because Nulls are by definition undefined values you can not use any of the
normal comparison operators.

Ron W

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and populate
the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options, but none seem
to
work. How do I eliminate the nulls, in other words I would like to sum
all
the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Fysh]

Yes I did look at using a query, but because I am using a for loop I didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" & False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID])

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and populate the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories could
be null altogether. Here is my code I have several options, but none seem to
work. How do I eliminate the nulls, in other words I would like to sum all
the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False & "
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Jeff Boyce]

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you won't query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" & False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID])

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and

populate
the

unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories could
be null altogether. Here is my code I have several options, but none

seem
to

work. How do I eliminate the nulls, in other words I would like to

sum
all

the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" &

False &
"

And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Fysh]

Thanks for the response. Here is the situation. I do a make table to group
the PractCATID from tblPracticalResults. The subform is unbound and I have
20 rows of buttons, textboxes, checkboxes, and labels. When I select the
student from the combo box it populates a couple hidden textboxes on the main
form. It then calls a function which the make table is performed and then a
For Loop. Inside this loop I have the items on the subform populated
corresponding to the PractCATID. One of the items I was trying to do is the
DSum to populate each total score textbox related to the PractCATID. I
thought the DSum would be the only way to do this. If not, how would you
suggest doing it in a query to do the same thing I am trying? Any
suggestions would be appreciated. Once again thanks.

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you won't query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" & False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID])

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual from a
combo box on the main form I have it loop through some code and populate
the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options, but none seem
to
work. How do I eliminate the nulls, in other words I would like to sum
all
the fields against the PracCatID, but skip the nulls. Could someone point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID = " &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Jeff Boyce]

The form of the query depends on the format of your data. Are you saying
that you have multiple fields in your table, each containing the same "kind"
of information (i.e., "score")? Or do you have a more-normalized design,
with one score per row? Getting a sum for the former design is problematic,
and subject to change whenever the number of scores changes. Getting a sum
for the latter design is a piece of cake, built into Access as a "Totals"
query.

What's in your database?

Jeff Boyce
<Access MVP>

Thanks for the response. Here is the situation. I do a make table to group
the PractCATID from tblPracticalResults. The subform is unbound and I have
20 rows of buttons, textboxes, checkboxes, and labels. When I select the
student from the combo box it populates a couple hidden textboxes on the main
form. It then calls a function which the make table is performed and then a
For Loop. Inside this loop I have the items on the subform populated
corresponding to the PractCATID. One of the items I was trying to do is the
DSum to populate each total score textbox related to the PractCATID. I
thought the DSum would be the only way to do this. If not, how would you
suggest doing it in a query to do the same thing I am trying? Any
suggestions would be appreciated. Once again thanks.

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you won't query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" & False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And

PractCATID =
" &

rst![PractCATID])

:

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an

individual
from a

combo box on the main form I have it loop through some code and populate
the
unbound textboxes on the subform with a score against a certain category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options, but

none
seem

to
work. How do I eliminate the nulls, in other words I would like

to
sum

all
the fields against the PracCatID, but skip the nulls. Could

someone
point

out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" & False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And

PractCATID =
" &

rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Fysh]

It would probably be easier for me to send it to you then actually trying to
explain it, the DB is less then 1 MB when zipped. Any way the subform and
all the controls are unbound. I have 20 rows of controls on the subform.
Each one is labeled like txt1, txt2, etc. The reason I have it like this is
because an individual can be tested on various topics and these topics can
change. So I use a For Loop to populate everything.
Now the table is normalized as far as I can tell. The table has the
following:
tblPracticalResults
PracticalResultsID PK
PracticalIDNumber FK
PractCATID Number
Attribute Text
Details Yes/No
PointsScored1 Number
PointsPossible1 Number
PointsGraded1 Yes/No
Etc.

I thought about putting a 0 (zero) as default for the PointsScored1 and the
loop works. However, I have another subform which is bound by a query and it
has combo boxes associated with each attribute based on the PractCATID that
uses the PointsScored1 and I don't want each one showing a 0 unless they were
actually graded with a 0.

Anyway it would be easier to show then trying to explain. Thanks for your
help so far.

The form of the query depends on the format of your data. Are you saying
that you have multiple fields in your table, each containing the same "kind"
of information (i.e., "score")? Or do you have a more-normalized design,
with one score per row? Getting a sum for the former design is problematic,
and subject to change whenever the number of scores changes. Getting a sum
for the latter design is a piece of cake, built into Access as a "Totals"
query.

What's in your database?

Jeff Boyce
<Access MVP>

Thanks for the response. Here is the situation. I do a make table to group
the PractCATID from tblPracticalResults. The subform is unbound and I have
20 rows of buttons, textboxes, checkboxes, and labels. When I select the
student from the combo box it populates a couple hidden textboxes on the main
form. It then calls a function which the make table is performed and then a
For Loop. Inside this loop I have the items on the subform populated
corresponding to the PractCATID. One of the items I was trying to do is the
DSum to populate each total score textbox related to the PractCATID. I
thought the DSum would be the only way to do this. If not, how would you
suggest doing it in a query to do the same thing I am trying? Any
suggestions would be appreciated. Once again thanks.

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you won't query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I
didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" &
False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID])

:

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert
Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual
from a
combo box on the main form I have it loop through some code and
populate
the
unbound textboxes on the subform with a score against a certain
category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options, but none
seem
to
work. How do I eliminate the nulls, in other words I would like to
sum
all
the fields against the PracCatID, but skip the nulls. Could someone
point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" &
False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Fysh]

I got it. Here is what I used.

varA = DSum(Nz("PointsScored1", 0), "tblPracticalResults", "Details =" &
False & " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID
= " & rst![PractCATID])

But for some reason I had to declare varA as a Variant. Why it work as a
double or currency. I mean it still works by why would one work over the
others? Just something I would like to know for future reference.

In the DSum reference in help it uses currency.

Thanks
Fysh

The form of the query depends on the format of your data. Are you saying
that you have multiple fields in your table, each containing the same "kind"
of information (i.e., "score")? Or do you have a more-normalized design,
with one score per row? Getting a sum for the former design is problematic,
and subject to change whenever the number of scores changes. Getting a sum
for the latter design is a piece of cake, built into Access as a "Totals"
query.

What's in your database?

Jeff Boyce
<Access MVP>

Thanks for the response. Here is the situation. I do a make table to group
the PractCATID from tblPracticalResults. The subform is unbound and I have
20 rows of buttons, textboxes, checkboxes, and labels. When I select the
student from the combo box it populates a couple hidden textboxes on the main
form. It then calls a function which the make table is performed and then a
For Loop. Inside this loop I have the items on the subform populated
corresponding to the PractCATID. One of the items I was trying to do is the
DSum to populate each total score textbox related to the PractCATID. I
thought the DSum would be the only way to do this. If not, how would you
suggest doing it in a query to do the same thing I am trying? Any
suggestions would be appreciated. Once again thanks.

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you won't query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I
didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" &
False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID])

:

Have you looked into using a query that "points" to the field(s) on your
form for criteria? Have you looked at the Nz() function to convert
Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual
from a
combo box on the main form I have it loop through some code and
populate
the
unbound textboxes on the subform with a score against a certain
category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options, but none
seem
to
work. How do I eliminate the nulls, in other words I would like to
sum
all
the fields against the PracCatID, but skip the nulls. Could someone
point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" &
False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID] & " And PointsScored1 <>" & Null)

 

[Jeff Boyce]

Variants can hold nulls.

Jeff

I got it. Here is what I used.

varA = DSum(Nz("PointsScored1", 0), "tblPracticalResults", "Details =" &
False & " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID
= " & rst![PractCATID])

But for some reason I had to declare varA as a Variant. Why it work as a
double or currency. I mean it still works by why would one work over the
others? Just something I would like to know for future reference.

In the DSum reference in help it uses currency.

Thanks
Fysh

The form of the query depends on the format of your data. Are you saying
that you have multiple fields in your table, each containing the same "kind"
of information (i.e., "score")? Or do you have a more-normalized design,
with one score per row? Getting a sum for the former design is problematic,
and subject to change whenever the number of scores changes. Getting a sum
for the latter design is a piece of cake, built into Access as a "Totals"
query.

What's in your database?

Jeff Boyce
<Access MVP>

Thanks for the response. Here is the situation. I do a make table to group
the PractCATID from tblPracticalResults. The subform is unbound and I have
20 rows of buttons, textboxes, checkboxes, and labels. When I select the
student from the combo box it populates a couple hidden textboxes on

the
main

form. It then calls a function which the make table is performed and

then
a

For Loop. Inside this loop I have the items on the subform populated
corresponding to the PractCATID. One of the items I was trying to do

is
the

DSum to populate each total score textbox related to the PractCATID. I
thought the DSum would be the only way to do this. If not, how would you
suggest doing it in a query to do the same thing I am trying? Any
suggestions would be appreciated. Once again thanks.

:

My suggestion of a query was intended to point out that "set"operations
(i.e., a query) can be much faster than iterating (looping) through a
recordset.

Using the Nz() function to convert any nulls to zeros in a query did not
work? Or have you decided that you must "loop", therefore, you

won't
query?

(In case you can't tell, I'm somewhat partial to using queries when ever
possible!)

Jeff Boyce
<Access MVP>

Yes I did look at using a query, but because I am using a for loop I
didn't
really think this was the best solution. I try Nz() function, but that
didn't seem to work either. Here is what I tried.

varA = DSum(Nz("PointsScored1"), "tblPracticalResults", "Details =" &
False
& " And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID])

:

Have you looked into using a query that "points" to the field(s)

on
your

form for criteria? Have you looked at the Nz() function to convert
Nulls to
....?

--
Good luck

Jeff Boyce
<Access MVP>

I have a form with an unbound subform. When I select an individual
from a
combo box on the main form I have it loop through some code and
populate
the
unbound textboxes on the subform with a score against a certain
category.
However, some of these scores could be null and some of the categories
could
be null altogether. Here is my code I have several options,

but
none

seem
to
work. How do I eliminate the nulls, in other words I would

like
to

sum
all
the fields against the PracCatID, but skip the nulls. Could someone
point
out waht my mistake is? Thanks in advance.

varA = DSum("PointsScored1", "tblPracticalResults", "Details =" &
False &
"
And PracticalIDNumber = " & Me.PracticalIDNumber & " And PractCATID =
" &
rst![PractCATID] & " And PointsScored1 <>" & Null)