DSUM with AND in access

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

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 &
"#")

Thomy5 wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

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

kingston wrote:
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

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 &
"#")

Thomy5 wrote:
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

--
Message posted via http://www.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!

kingston wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

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
http://I.Am/DougSteele
(no e-mails, please!)


"Thomy5 via AccessMonster.com" u30166@uwe wrote in message
news:6ab4034db717c@uwe...
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!

kingston wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

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

Douglas J. Steele wrote:
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

--
Message posted via http://www.accessmonster.com

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Thomy5 via AccessMonster.com" u30166@uwe wrote in message
news:6ab4977937920@uwe...
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

Douglas J. Steele wrote:
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

--
Message posted via http://www.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

Thomy5 wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

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"

Douglas J. Steele wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1