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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|