DSUM with AND in access

[Thomy5]

I'm trying to total a field in a table called tblAbsense for an employeeID.
the field i'm totaling is called Under31 and i only want to total the records
before a given date. the employeeID and the given date are displayed in a
forms current record opened and the total is put in a Dim in some VBA code.
It looks like this:

Dim intUnder31 As Integer
intUnder31 = DSum("[Under31]", "tblAbsense", "[EmployeeID] = '" & Me.
EmployeeID & "' AND_ [Start_Date_Of_Absense] < ' " & Me.Start_Date_of_Absense
& " ' ")
It keeps throwing up an error that says "Invalid use of null"
Can anyone help me?

Thanks

-Thomy5

 

[kingston via AccessMonster.com]

Is there a Null in the data: [Under31], [EmployeeID] or [Start_Date...]? I
assume that there are valid values for Me.EmployeeID and Me.Start_Date...
Note minor syntax corrections:

intUnder31 = DSum("[Under31]", "tblAbsense", "[EmployeeID] = '" & Me.
EmployeeID & "' AND [Start_Date_Of_Absense] < #" & Me.Start_Date_of_Absense &
"#")

I'm trying to total a field in a table called tblAbsense for an employeeID.
the field i'm totaling is called Under31 and i only want to total the records
before a given date. the employeeID and the given date are displayed in a
forms current record opened and the total is put in a Dim in some VBA code.
It looks like this:

Dim intUnder31 As Integer
intUnder31 = DSum("[Under31]", "tblAbsense", "[EmployeeID] = '" & Me.
EmployeeID & "' AND_ [Start_Date_Of_Absense] < ' " & Me.Start_Date_of_Absense
& " ' ")
It keeps throwing up an error that says "Invalid use of null"
Can anyone help me?

Thanks

-Thomy5

 

[Thomy5]

Kingston
thanks for the reply. I tried the change and it still coughs up the same
error. And in answer to your assumption, you assume correctly. The values
even show up when i hover my curser over them in the code when debugging. one
says, Me.EmployeeID = "doej" and the other says, Me.Start_Date_Of_Absense =
9/24/2006. my brain is turning to jello.

-Thomy5

Is there a Null in the data: [Under31], [EmployeeID] or [Start_Date...]? I
assume that there are valid values for Me.EmployeeID and Me.Start_Date...
Note minor syntax corrections:

intUnder31 = DSum("[Under31]", "tblAbsense", "[EmployeeID] = '" & Me.
EmployeeID & "' AND [Start_Date_Of_Absense] < #" & Me.Start_Date_of_Absense &
"#")

I'm trying to total a field in a table called tblAbsense for an employeeID.
the field i'm totaling is called Under31 and i only want to total the records

[quoted text clipped - 12 lines]

-Thomy5

 

[kingston via AccessMonster.com]

Did you check for Nulls in the data? You might be able to use the function
Nz() to trap Nulls but I don't know what you want to replace them with:

intUnder31 = DSum("Nz([Under31],0)", "tblAbsense", "Nz([EmployeeID]," ") = '"
& Me.
EmployeeID & "' AND Nz([Start_Date_Of_Absense],#1/1/2006#) < #" & Me.
Start_Date_of_Absense &
"#")

Kingston
thanks for the reply. I tried the change and it still coughs up the same
error. And in answer to your assumption, you assume correctly. The values
even show up when i hover my curser over them in the code when debugging. one
says, Me.EmployeeID = "doej" and the other says, Me.Start_Date_Of_Absense =
9/24/2006. my brain is turning to jello.

-Thomy5

Is there a Null in the data: [Under31], [EmployeeID] or [Start_Date...]? I
assume that there are valid values for Me.EmployeeID and Me.Start_Date...

[quoted text clipped - 9 lines]

 

[Thomy5 via AccessMonster.com]

Hi Kingston,
Very interesting. I read up on this Nz stuff. the EmployeeID i wanted was
doej and the dates i wanted were before 9/24/2006, so using your example i
changed the code to:

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],doej) = '"
& Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#) < # " &
dteSDate & " # ")

now it spits out an error message stating "You canceled the previous
operation" If i am understanding this correctly, what we told it to do is if
the information in the EmployeeID and Start_Date_Of_Absense controles on the
form record open can't be read by the DSUM statement, it will use doej for
the EmployeeID and 9/24/2006 for the Start_Date_Of_Absense, right? Hmmm, Even
though i'm dreaming of a white christmas, this problem will drive me to drink
the red if i run out of white!

Did you check for Nulls in the data? You might be able to use the function
Nz() to trap Nulls but I don't know what you want to replace them with:

intUnder31 = DSum("Nz([Under31],0)", "tblAbsense", "Nz([EmployeeID]," ") = '"
& Me.
EmployeeID & "' AND Nz([Start_Date_Of_Absense],#1/1/2006#) < #" & Me.
Start_Date_of_Absense &
"#")

Kingston
thanks for the reply. I tried the change and it still coughs up the same

[quoted text clipped - 10 lines]

 

[Douglas J. Steele]

That misleading error message generally means that you've mistyped a name in
your statement. Double check the spelling of all field and table names.

What is doej? If it's a field in the table, I'd recommend putting square
brackets around it

"Nz([EmployeeID],[doej]) = ..."

If it's a variable, it needs to be outside of the quotes

"Nz([EmployeeID],'" & doej & "') = ..."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Kingston,
Very interesting. I read up on this Nz stuff. the EmployeeID i wanted was
doej and the dates i wanted were before 9/24/2006, so using your example i
changed the code to:

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],doej) =
'"
& Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#) < # " &
dteSDate & " # ")

now it spits out an error message stating "You canceled the previous
operation" If i am understanding this correctly, what we told it to do is
if
the information in the EmployeeID and Start_Date_Of_Absense controles on
the
form record open can't be read by the DSUM statement, it will use doej for
the EmployeeID and 9/24/2006 for the Start_Date_Of_Absense, right? Hmmm,
Even
though i'm dreaming of a white christmas, this problem will drive me to
drink
the red if i run out of white!

Did you check for Nulls in the data? You might be able to use the
function
Nz() to trap Nulls but I don't know what you want to replace them with:

intUnder31 = DSum("Nz([Under31],0)", "tblAbsense", "Nz([EmployeeID]," ") =
'"
& Me.
EmployeeID & "' AND Nz([Start_Date_Of_Absense],#1/1/2006#) < #" & Me.
Start_Date_of_Absense &
"#")

Kingston
thanks for the reply. I tried the change and it still coughs up the same

[quoted text clipped - 10 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use in the
dsum formula and ignore the rest of the employees when totaling the Under31
field. Just for a little clarification, this is an employee absense database
that i made that mesures the absenses and tardies in units of "points". Every
absense = 1 point except for when they're tardy. the employees are bus
drivers for a public transit company. as you know punctuality is important
because if your bus driver is late for work, so are you along with the other
40 people on the bus. the Under31 field in the record is 0 if it is a record
of a sick day or personal day, but when they're late it populates a value of
1. this is because the rest of the code sets the policy negotiated by the
union contract, ie. the first 2 tardies < 31 minutes are 0 points, the next 4
tardies < 16 minutes are half a point and everything else is 1 point. this is
a way of messuring performance and rewarding or retraining our employees. To
determine weather to assign a 0, 1/2, or full point to a record depends on
how many tardies they've had. hence, the sum of the Under31 records for a
specific employee before the date of the record opened will return the proper
value in the Points field.
I hope this sheds light on something i missed or perhaps i need to go about
this in a different way.

Cheers! - Thomy5

That misleading error message generally means that you've mistyped a name in
your statement. Double check the spelling of all field and table names.

What is doej? If it's a field in the table, I'd recommend putting square
brackets around it

"Nz([EmployeeID],[doej]) = ..."

If it's a variable, it needs to be outside of the quotes

"Nz([EmployeeID],'" & doej & "') = ..."

Hi Kingston,
Very interesting. I read up on this Nz stuff. the EmployeeID i wanted was

[quoted text clipped - 34 lines]

 

[Douglas J. Steele]

So what does your code look like now, and does it work?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use in
the
dsum formula and ignore the rest of the employees when totaling the
Under31
field. Just for a little clarification, this is an employee absense
database
that i made that mesures the absenses and tardies in units of "points".
Every
absense = 1 point except for when they're tardy. the employees are bus
drivers for a public transit company. as you know punctuality is important
because if your bus driver is late for work, so are you along with the
other
40 people on the bus. the Under31 field in the record is 0 if it is a
record
of a sick day or personal day, but when they're late it populates a value
of
1. this is because the rest of the code sets the policy negotiated by the
union contract, ie. the first 2 tardies < 31 minutes are 0 points, the
next 4
tardies < 16 minutes are half a point and everything else is 1 point. this
is
a way of messuring performance and rewarding or retraining our employees.
To
determine weather to assign a 0, 1/2, or full point to a record depends on
how many tardies they've had. hence, the sum of the Under31 records for a
specific employee before the date of the record opened will return the
proper
value in the Points field.
I hope this sheds light on something i missed or perhaps i need to go
about
this in a different way.

Cheers! - Thomy5

That misleading error message generally means that you've mistyped a name
in
your statement. Double check the spelling of all field and table names.

What is doej? If it's a field in the table, I'd recommend putting square
brackets around it

"Nz([EmployeeID],[doej]) = ..."

If it's a variable, it needs to be outside of the quotes

"Nz([EmployeeID],'" & doej & "') = ..."

Hi Kingston,
Very interesting. I read up on this Nz stuff. the EmployeeID i wanted
was

[quoted text clipped - 34 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

Oh, another thing, this DSUM code runs great if i don't add the AND for the
date thing. it will nicely total all of the under31 for an employee. that's
no good if someone goes to an early record of an employee and on focus of the
points controle the event proceedure runs the code as it's now writen. even
though up to that date they may have only had 1 tardy and it should be 0
points assigned to that record, it totals all the under31 for that employee
and returns a 1 for the point if they've been tardy over the past year more
than 6 times.

-Thomy5

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use in the
dsum formula and ignore the rest of the employees when totaling the Under31
field. Just for a little clarification, this is an employee absense database
that i made that mesures the absenses and tardies in units of "points". Every
absense = 1 point except for when they're tardy. the employees are bus
drivers for a public transit company. as you know punctuality is important
because if your bus driver is late for work, so are you along with the other
40 people on the bus. the Under31 field in the record is 0 if it is a record
of a sick day or personal day, but when they're late it populates a value of
1. this is because the rest of the code sets the policy negotiated by the
union contract, ie. the first 2 tardies < 31 minutes are 0 points, the next 4
tardies < 16 minutes are half a point and everything else is 1 point. this is
a way of messuring performance and rewarding or retraining our employees. To
determine weather to assign a 0, 1/2, or full point to a record depends on
how many tardies they've had. hence, the sum of the Under31 records for a
specific employee before the date of the record opened will return the proper
value in the Points field.
I hope this sheds light on something i missed or perhaps i need to go about
this in a different way.

Cheers! - Thomy5

That misleading error message generally means that you've mistyped a name in
your statement. Double check the spelling of all field and table names.

[quoted text clipped - 13 lines]

 

[Thomy5 via AccessMonster.com]

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID], doej ) =
'" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#) < # " &
dteSDate & " # ")

I tried this:

'" & doej & "'

but it returned an error " Variable not defined"

So what does your code look like now, and does it work?

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use in

[quoted text clipped - 50 lines]

 

[Douglas J. Steele]

Since doej is a variable, it must be outside the quotes: it definitely will
not work inside the quotes, as DSum knows nothing about VBA variables.

Where have your declared doej (and how have you declared it)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID], doej )
=
'" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#) < # "
&
dteSDate & " # ")

I tried this:

'" & doej & "'

but it returned an error " Variable not defined"

So what does your code look like now, and does it work?

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use
in

[quoted text clipped - 50 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

Douglas,

doej is an EmployeeID i use for testing. there are several records belonging
to doej. i changed the code to :

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],'" & "doej"
& "') = '" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#) <
# " & dteSDate & " # ")

and the error is back to " invalid use of Null!"

groan!

Since doej is a variable, it must be outside the quotes: it definitely will
not work inside the quotes, as DSum knows nothing about VBA variables.

Where have your declared doej (and how have you declared it)?

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID], doej )
=

[quoted text clipped - 16 lines]

 

[Douglas J. Steele]

That's not what I said to do. Remove the quotes around doej:

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],'" & doej
& "') = '" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#)
<
# " & dteSDate & " # ")

If that still doesn't work, then please answer my question about where doej
is declared.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

doej is an EmployeeID i use for testing. there are several records
belonging
to doej. i changed the code to :

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],'" &
"doej"
& "') = '" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#)
<
# " & dteSDate & " # ")

and the error is back to " invalid use of Null!"

groan!

Since doej is a variable, it must be outside the quotes: it definitely
will
not work inside the quotes, as DSum knows nothing about VBA variables.

Where have your declared doej (and how have you declared it)?

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],
doej )
=

[quoted text clipped - 16 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

When i enter it as :

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID], ' " & doej
& " ' ) = '" & Me.EmployeeID & "' AND Nz([Start_Date_Of_Absense],#9/24/2006#)
< # " & dteSDate & " # ")

it returns an error " Variable not defined" with doej highlighted.
I'm kind of new at this and mostly self taught. could you please explain what
you mean by "where doej is declaired"?

That's not what I said to do. Remove the quotes around doej:

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],'" & doej
& "') = '" & Me.EmployeeID & "'AND Nz([Start_Date_Of_Absense],#9/24/2006#)
<
# " & dteSDate & " # ")

If that still doesn't work, then please answer my question about where doej
is declared.

Douglas,

[quoted text clipped - 24 lines]

 

[Douglas J. Steele]

Do you have a statement

Dim doej As String

anywhere in your code?

If so, where: in the same routine as where this code is, or in some other
routine?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When i enter it as :

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID], ' " &
doej
& " ' ) = '" & Me.EmployeeID & "' AND
Nz([Start_Date_Of_Absense],#9/24/2006#)
< # " & dteSDate & " # ")

it returns an error " Variable not defined" with doej highlighted.
I'm kind of new at this and mostly self taught. could you please explain
what
you mean by "where doej is declaired"?

That's not what I said to do. Remove the quotes around doej:

intUnder31 = DSum("Nz([Under31])", "tblAbsense", "Nz([EmployeeID],'" &
doej
& "') = '" & Me.EmployeeID & "'AND
Nz([Start_Date_Of_Absense],#9/24/2006#)
<
# " & dteSDate & " # ")

If that still doesn't work, then please answer my question about where
doej
is declared.

Douglas,

[quoted text clipped - 24 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

When I open the frmAbsense form and am entering a record for, say, doej, one
of our employees, doej is in the controle for EmployeeID. I then enter the
date of the absense or tardy in the Start_Date_Of_Absense controle and the
status of doej as being tardy in the status controle, and this will
automaticaly populate the Under31 controle with a value of 1. If, for example,
the status were Sick instead, the Under31 controle will default to Zero. My
DSUM code should look to the tblAbsense table for all of doej records before
the date in the Start_Date_Of_Absense controle in my open form and sum the
Under31 field to tell me how many tardies doej has had up to that date. As i
understand Nz, and I may be wrong, isn't my code telling DSUM to look to the
EmployeeID controle of the form that I have open and have populated with doej
and use that in the WHERE part of the formula when searching the tblAbsense
table for all records matching doej, and if the ME.EMPLOYEEID is null, it
will use instead the value of doej in searching the tblAbsense table? I'm
just using the Nz to troubleshoot the code to find the null that is causing
the code to pass the null onto the intunder31 DIM. I read that the DSUM
returning a null to the intUnder31 DIM is usualy the result of a problem with
the WHERE part of the code. it worked fine until i included the AND part of
the statement with the Start_Date_Of_Absense to narrow my search when
totaling the Under31 values. Just for kicks and giggles, i created a DIM
called doej as String and then assigned it a value of "doej" like you
suggested. and I'm back to the error message "invalid use of null". puzzling
huh? when i debug the code and hover my curser over the doej part of the code
it shows it as doej = "doej" and when hovering over the dteSDate as dteSDate
= 9/24/2006. Why doesn't Access 2003 like AND in it's criteria? I've been
working on this one line of code for over 3 days! Forgive my venting Douglas,
i really do appreciate your insight and suggestions. Keep them cards and
letters coming.

Cheers!

Do you have a statement

Dim doej As String

anywhere in your code?

If so, where: in the same routine as where this code is, or in some other
routine?

When i enter it as :

[quoted text clipped - 27 lines]

 

[Douglas J. Steele]

DSUM doesn't work with controls on a form: it works with data in a table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I open the frmAbsense form and am entering a record for, say, doej,
one
of our employees, doej is in the controle for EmployeeID. I then enter the
date of the absense or tardy in the Start_Date_Of_Absense controle and the
status of doej as being tardy in the status controle, and this will
automaticaly populate the Under31 controle with a value of 1. If, for
example,
the status were Sick instead, the Under31 controle will default to Zero.
My
DSUM code should look to the tblAbsense table for all of doej records
before
the date in the Start_Date_Of_Absense controle in my open form and sum the
Under31 field to tell me how many tardies doej has had up to that date. As
i
understand Nz, and I may be wrong, isn't my code telling DSUM to look to
the
EmployeeID controle of the form that I have open and have populated with
doej
and use that in the WHERE part of the formula when searching the
tblAbsense
table for all records matching doej, and if the ME.EMPLOYEEID is null, it
will use instead the value of doej in searching the tblAbsense table? I'm
just using the Nz to troubleshoot the code to find the null that is
causing
the code to pass the null onto the intunder31 DIM. I read that the DSUM
returning a null to the intUnder31 DIM is usualy the result of a problem
with
the WHERE part of the code. it worked fine until i included the AND part
of
the statement with the Start_Date_Of_Absense to narrow my search when
totaling the Under31 values. Just for kicks and giggles, i created a DIM
called doej as String and then assigned it a value of "doej" like you
suggested. and I'm back to the error message "invalid use of null".
puzzling
huh? when i debug the code and hover my curser over the doej part of the
code
it shows it as doej = "doej" and when hovering over the dteSDate as
dteSDate
= 9/24/2006. Why doesn't Access 2003 like AND in it's criteria? I've been
working on this one line of code for over 3 days! Forgive my venting
Douglas,
i really do appreciate your insight and suggestions. Keep them cards and
letters coming.

Cheers!

Do you have a statement

Dim doej As String

anywhere in your code?

If so, where: in the same routine as where this code is, or in some other
routine?

When i enter it as :

[quoted text clipped - 27 lines]

-Thomy5

 

[Thomy5 via AccessMonster.com]

True, but you can uses the controles on a form to tell DSUM which variables
to look for instead of using a static value in the DSUM code. it makes it
more user friendy.

DSUM doesn't work with controls on a form: it works with data in a table.

When I open the frmAbsense form and am entering a record for, say, doej,
one

[quoted text clipped - 56 lines]

 

[Thomy5 via AccessMonster.com]

Douglas,

I Got it!
You hit the nail on the head when you told me to look for typos.
Many thanks to all those who were trying to help. the problem wasn't in the
dsum code, it was in the criteria. specificaly in the AND that i added, the
[Start_date_of_absense] should have been [Start date of absense]. I only
needed the underscore thingys when stating the controle name outside of
brackets, like Me.Start_date_of_absense.

Onward and Upward!

Oh, another thing, this DSUM code runs great if i don't add the AND for the
date thing. it will nicely total all of the under31 for an employee. that's
no good if someone goes to an early record of an employee and on focus of the
points controle the event proceedure runs the code as it's now writen. even
though up to that date they may have only had 1 tardy and it should be 0
points assigned to that record, it totals all the under31 for that employee
and returns a 1 for the point if they've been tardy over the past year more
than 6 times.

-Thomy5

Hi Douglas,
doej is a variable. it's an employee ID and the one that i want to use in the

[quoted text clipped - 24 lines]