A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DSUM with AND in access



 
 
Thread Tools Display Modes
  #11  
Old December 13th, 2006, 07:24 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default DSUM with AND in access

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


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



  #12  
Old December 13th, 2006, 08:08 PM posted to microsoft.public.access.forms
Thomy5 via AccessMonster.com
external usenet poster
 
Posts: 9
Default DSUM with AND in access

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!

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


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

  #13  
Old December 13th, 2006, 09:26 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default DSUM with AND in access

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


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

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


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



  #14  
Old December 13th, 2006, 09:43 PM posted to microsoft.public.access.forms
Thomy5 via AccessMonster.com
external usenet poster
 
Posts: 9
Default DSUM with AND in access

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"?

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


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

  #15  
Old December 14th, 2006, 12:44 AM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default DSUM with AND in access

Do you have a statement

Dim doej As String

anywhere in your code?

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

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


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

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


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



  #16  
Old December 14th, 2006, 01:17 PM posted to microsoft.public.access.forms
Thomy5 via AccessMonster.com
external usenet poster
 
Posts: 9
Default DSUM with AND in access

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!

Douglas J. Steele wrote:
Do you have a statement

Dim doej As String

anywhere in your code?

If so, whe 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


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

  #17  
Old December 14th, 2006, 02:24 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default DSUM with AND in access

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

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


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

Douglas J. Steele wrote:
Do you have a statement

Dim doej As String

anywhere in your code?

If so, whe 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


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



  #18  
Old December 14th, 2006, 02:58 PM posted to microsoft.public.access.forms
Thomy5 via AccessMonster.com
external usenet poster
 
Posts: 9
Default DSUM with AND in access

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.

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


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

  #19  
Old December 14th, 2006, 08:44 PM posted to microsoft.public.access.forms
Thomy5 via AccessMonster.com
external usenet poster
 
Posts: 9
Default DSUM with AND in access

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!

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

-Thomy5


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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.