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
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
The actual code is:
Private Sub Form_Current() Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub -- Judy "John W. Vinson" wrote: On Wed, 17 Mar 2010 08:13:02 -0700, judyb wrote: Hi Steve, Yes, I finally located that post and did as you suggested. It is now giving me a Run-time error "3061": Too few parameters. Expected 2. When I select the "Debug" button and my code opens, the following text is highlighted: Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset) I am lost. This is my first database so I am really green. Got any suggestions? Please post the actual code. It looks like your syntax is wrong! Click the mouse on the word OpenRecordset and press F1 and study the help message for the correct form of the OpenRecordset event. -- John W. Vinson [MVP] . |
#12
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
The actual code is:
Private Sub Form_Current() Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub This code worked fine until I made the following suggested changes: Add an unbound textbox to the form where you get [Current_Department_Name}. Name the textbox DepartmentNameCriteria and set its Visible property to No. Put the following expression in its Controlsource property: =IIF([Current_Department_Name] = "Reserves", Null,[Current_Department_Name]) Rather than use the DSum function, change your Service Record Query to a Totals query. Click on the Sigma button (looks like a capital E) in the menu at the top of the screen. Change Group By under WeeksService to Sum. Change Group By under DepartName to Where. Put the following expression in the criteria under DepartmentName: Forms!NameOfYourForm!DepartmentNameCriteria Or (Forms!NameOfYourFormDepartmentNameCriteria Is Null) -- Judy "J_Goddard via AccessMonster.com" wrote: Either the syntax is wrong as suggested, or "s" (which must be a string containing an SQL SELECT... statement) refers to a non-existant field somewhere. What is "s", and what does it contain? John G. judyb wrote: Hi Steve, Yes, I finally located that post and did as you suggested. It is now giving me a Run-time error "3061": Too few parameters. Expected 2. When I select the "Debug" button and my code opens, the following text is highlighted: Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset) I am lost. This is my first database so I am really green. Got any suggestions? Judy, [quoted text clipped - 43 lines] . -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via http://www.accessmonster.com . |
#13
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
On Wed, 17 Mar 2010 10:39:01 -0700, judyb
wrote: The actual code is: Private Sub Form_Current() Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub It sounds like either Service Record Query has two parameters that you're not resolving, or that it does not include two of the fields you're selecting. Could you post the SQL of this query? -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
Hi Judy,
First, let's make sure [Service Record Query] is working properly. In the code below, add the two new lines right after Private Sub .... The new code will open the query and bypass everything else in the code. Steve Private Sub Form_Current() DoCmd.OpenQuery "Service Record Query" Exit Sub Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub "judyb" wrote in message news Hi Steve, Yes, I finally located that post and did as you suggested. It is now giving me a Run-time error "3061": Too few parameters. Expected 2. When I select the "Debug" button and my code opens, the following text is highlighted: Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset) I am lost. This is my first database so I am really green. Got any suggestions? -- Judy "Steve" wrote: Judy, Did you try changing your query to a totals query as I previously suggested? Steve "judyb" wrote in message ... John & Steve, I apologize for reposting. I was unable to find my first post. Anyway, I thank you both for responding. I copied and pasted the new code, but it did not provide the correct answer. In fact, the answer was way too high (in the thousands) where it should have been below 100. Any other suggestions? Thanks again! -- Judy "Steve" wrote: I don't think your DSum function gives the OP the answer she wants! If the current department name is millwright, she wants total WeeksService for Millwright. The same for Pipefitter. But if the current department name is Reserves, then she wants total WeeksService for MillWright and Pipefitter. Steve "John W. Vinson" wrote in message ... On Fri, 12 Mar 2010 10:56:01 -0800, judyb wrote: I hope this makes sense. In short, I need to keep the existing code, but need it to sum the Weeks Service slightly different IF the Current Department Name is equal to "Reserves". Thanks in advance! Perhaps you missed the answers that Steve and I posted last week. Here you go again: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves'") -- John W. Vinson [MVP] . . |
#15
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
Hi Steve,
When you say "add the two new lines of code", are you referring to the following code? Forms!NameOfYourForm!DepartmentNameCriteria Or (Forms!NameOfYourForm!DepartmentNameCriteria Is Null) -- Judy "Steve" wrote: Hi Judy, First, let's make sure [Service Record Query] is working properly. In the code below, add the two new lines right after Private Sub .... The new code will open the query and bypass everything else in the code. Steve Private Sub Form_Current() DoCmd.OpenQuery "Service Record Query" Exit Sub Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub "judyb" wrote in message news Hi Steve, Yes, I finally located that post and did as you suggested. It is now giving me a Run-time error "3061": Too few parameters. Expected 2. When I select the "Debug" button and my code opens, the following text is highlighted: Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset) I am lost. This is my first database so I am really green. Got any suggestions? -- Judy "Steve" wrote: Judy, Did you try changing your query to a totals query as I previously suggested? Steve "judyb" wrote in message ... John & Steve, I apologize for reposting. I was unable to find my first post. Anyway, I thank you both for responding. I copied and pasted the new code, but it did not provide the correct answer. In fact, the answer was way too high (in the thousands) where it should have been below 100. Any other suggestions? Thanks again! -- Judy "Steve" wrote: I don't think your DSum function gives the OP the answer she wants! If the current department name is millwright, she wants total WeeksService for Millwright. The same for Pipefitter. But if the current department name is Reserves, then she wants total WeeksService for MillWright and Pipefitter. Steve "John W. Vinson" wrote in message ... On Fri, 12 Mar 2010 10:56:01 -0800, judyb wrote: I hope this makes sense. In short, I need to keep the existing code, but need it to sum the Weeks Service slightly different IF the Current Department Name is equal to "Reserves". Thanks in advance! Perhaps you missed the answers that Steve and I posted last week. Here you go again: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves'") -- John W. Vinson [MVP] . . . |
#16
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
No! Add these two lines of code in Private Sub Form_Current() as shown below
...... DoCmd.OpenQuery "Service Record Query" Exit Sub Steve "judyb" wrote in message ... Hi Steve, When you say "add the two new lines of code", are you referring to the following code? Forms!NameOfYourForm!DepartmentNameCriteria Or (Forms!NameOfYourForm!DepartmentNameCriteria Is Null) -- Judy "Steve" wrote: Hi Judy, First, let's make sure [Service Record Query] is working properly. In the code below, add the two new lines right after Private Sub .... The new code will open the query and bypass everything else in the code. Steve Private Sub Form_Current() DoCmd.OpenQuery "Service Record Query" --------------------- Exit Sub -------------------- Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub "judyb" wrote in message news Hi Steve, Yes, I finally located that post and did as you suggested. It is now giving me a Run-time error "3061": Too few parameters. Expected 2. When I select the "Debug" button and my code opens, the following text is highlighted: Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset) I am lost. This is my first database so I am really green. Got any suggestions? -- Judy "Steve" wrote: Judy, Did you try changing your query to a totals query as I previously suggested? Steve "judyb" wrote in message ... John & Steve, I apologize for reposting. I was unable to find my first post. Anyway, I thank you both for responding. I copied and pasted the new code, but it did not provide the correct answer. In fact, the answer was way too high (in the thousands) where it should have been below 100. Any other suggestions? Thanks again! -- Judy "Steve" wrote: I don't think your DSum function gives the OP the answer she wants! If the current department name is millwright, she wants total WeeksService for Millwright. The same for Pipefitter. But if the current department name is Reserves, then she wants total WeeksService for MillWright and Pipefitter. Steve "John W. Vinson" wrote in message ... On Fri, 12 Mar 2010 10:56:01 -0800, judyb wrote: I hope this makes sense. In short, I need to keep the existing code, but need it to sum the Weeks Service slightly different IF the Current Department Name is equal to "Reserves". Thanks in advance! Perhaps you missed the answers that Steve and I posted last week. Here you go again: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves'") -- John W. Vinson [MVP] . . . |
#17
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
John,
I used the code below that you posted earlier in the form. It is caluclating without any error messages, but it is giving me a number that is far to high. The sum should be 83 and it is showing 30,987. Therefore, I know that it is pulling Weeks Service from more records than it should. Earlier Code Posted: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves'") Now for the SQL from the query (Service Record Query) that you requested in this post: SELECT [Service Record].ServiceRecordID, [Service Record].EmployeeID, [Job Titles].LOP, [Service Record].JobTitleName, [Service Record].DepartmentName, [Service Record].DateStart, [Service Record].DateEnd, Sum(CalcWeeks([DateStart],[DateEnd])) AS WeeksService, [Service Record].Reason, IIf(IsNull([DateEnd]),"Current","Previous") AS Status, [Service Record].Comments FROM [Service Record] INNER JOIN [Job Titles] ON [Service Record].JobTitleName = [Job Titles].JobTitleName GROUP BY [Service Record].ServiceRecordID, [Service Record].EmployeeID, [Job Titles].LOP, [Service Record].JobTitleName, [Service Record].DepartmentName, [Service Record].DateStart, [Service Record].DateEnd, [Service Record].Reason, IIf(IsNull([DateEnd]),"Current","Previous"), [Service Record].Comments ORDER BY [Service Record].EmployeeID; Thanks so much for your help! -- Judy "John W. Vinson" wrote: On Wed, 17 Mar 2010 10:39:01 -0700, judyb wrote: The actual code is: Private Sub Form_Current() Dim s As String Dim rs As DAO.Recordset s = "SELECT JobTitleName,DepartmentName,LOP" & _ " FROM [Service Record Query] WHERE EmployeeID = " _ & Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null" If Not Me.NewRecord Then Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset) If Not (rs.BOF And rs.EOF) Then Me.Current_Job_Title_Name = rs!JobTitleName Me.Current_Department_Name = rs!DepartmentName Me.Current_LOP = rs!LOP rs.Close Set rs = Nothing Else Me.Current_Job_Title_Name = Null Me.Current_Department_Name = Null End If End If End Sub It sounds like either Service Record Query has two parameters that you're not resolving, or that it does not include two of the fields you're selecting. Could you post the SQL of this query? -- John W. Vinson [MVP] . |
#18
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
On Thu, 18 Mar 2010 10:58:02 -0700, judyb
wrote: John, I used the code below that you posted earlier in the form. It is caluclating without any error messages, but it is giving me a number that is far to high. The sum should be 83 and it is showing 30,987. Therefore, I know that it is pulling Weeks Service from more records than it should. Earlier Code Posted: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves'") Aha. You need some parentheses - as is it's finding ALL records ever for Reserves, not just for this employee: Earlier Code Posted: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And ([DepartmentName] = Current_Department_Name OR [DepartmentName] = 'Reserves')") That's a guess and may still not be correct. -- John W. Vinson [MVP] |
#19
|
|||
|
|||
Is it possible to run "If Statement" with DSum?
Judy:
I think you may have been following a red herring here and you can achieve what you want with a simple extension of the expression. Going back to your original expression: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I assume that Current_Department_Name is a string variable, so this would be better expressed as the following, concatenating the value of the variable into the expression: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = """ & Current_Department_Name & """") If we extend the logic to sum the WeeksService value for all rows regardless of department where the value of the Current_Department_Name variable is 'Reserves' it becomes: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And ([DepartmentName] = """ & Current_Department_Name & """ Or """ & Current_Department_Name & """ = ""Reserves"")") Say that the current EmployeeID value is 42 and the current value of the Current_Department_Name variable is 'Millwright' the criteria expression would evaluate as: [EmployeeID] = 42 And ([DepartmentName] = "Millwright" Or "Millwright" = "Reserves") So the first part of the Boolean Or operation is True for any row where the DepartmentName value is 'Millwright'. The second part of the operation is always False, but as an Or operation only requires one part to be True all rows where EmployeeID is 42 and the DepartmentName value is 'Millwright' will be summed. Say on the other hand that the current EmployeeID value is 42 and the current value of the Current_Department_Name variable is 'Reserves' the criteria expression would evaluate as: [EmployeeID] = 42 And ([DepartmentName] = "Reserves" Or "Reserves" = "Reserves") In this case the second part of the Boolean Or operation is True under any circumstances, so every row will be summed where EmployeeID = 42 regardless of the DepartmentName value. Ken Sheridan Stafford, England judyb wrote: Hi Steve, When you say "add the two new lines of code", are you referring to the following code? Forms!NameOfYourForm!DepartmentNameCriteria Or (Forms!NameOfYourForm!DepartmentNameCriteria Is Null) Hi Judy, [quoted text clipped - 99 lines] . -- Message posted via http://www.accessmonster.com |
|
Thread Tools | |
Display Modes | |
|
|