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 |
#1
|
|||
|
|||
Changing existing code to add "IF Statement"
I am currently using a database that is working well. However, I have
discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy |
#2
|
|||
|
|||
Changing existing code to add "IF Statement"
This is aircode, but if I understand your question correctly, you need
another query to return ALL weeks instead of what it presently does. Then: If Me.Current_Deparyment_Name = "Reserves" Then =DSum("[WeeksService]","Service Record Query2","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") Else =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") End If Notice a different query: "Service Record Query2" which will return all weeks instead of specific ones. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy |
#3
|
|||
|
|||
Changing existing code to add "IF Statement"
ztuiolökujzhjklrdmncfgvbvjh ghhgfdghtgcfdf
"judyb" schrieb im Newsbeitrag ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy |
#4
|
|||
|
|||
Changing existing code to add "IF Statement"
Arvin,
Thanks for responding. After rereading my post, I find that I wasn't very clear. I'll try again. The existing code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I do want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL Department Name IF the Current Department Name is equal to "Reserves". Example: Department Name Weeks Service Millwright 35 weeks Pipefitter 10 weeks Millwright (Current Deptment Name) 3 weeks The existing code adds all the Weeks Service where the Department Name is equal to the Current Department (Millwright) for a total of 38. In the same example, if the Current Department were "Reserves", I need code that will add ALL of the Weeks Service of ALL Department Name to give me a total of 48. I hope this makes more 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 Again! -- Judy "Arvin Meyer [MVP]" wrote: This is aircode, but if I understand your question correctly, you need another query to return ALL weeks instead of what it presently does. Then: If Me.Current_Deparyment_Name = "Reserves" Then =DSum("[WeeksService]","Service Record Query2","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") Else =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") End If Notice a different query: "Service Record Query2" which will return all weeks instead of specific ones. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy . |
#5
|
|||
|
|||
Changing existing code to add "IF Statement"
Hi Judy,
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) Steve "judyb" wrote in message ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy |
#6
|
|||
|
|||
Changing existing code to add "IF Statement"
Yes, you just need a new query which shows ALL departments. Department Name
doesn't need to be a part of the query. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... Arvin, Thanks for responding. After rereading my post, I find that I wasn't very clear. I'll try again. The existing code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I do want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL Department Name IF the Current Department Name is equal to "Reserves". Example: Department Name Weeks Service Millwright 35 weeks Pipefitter 10 weeks Millwright (Current Deptment Name) 3 weeks The existing code adds all the Weeks Service where the Department Name is equal to the Current Department (Millwright) for a total of 38. In the same example, if the Current Department were "Reserves", I need code that will add ALL of the Weeks Service of ALL Department Name to give me a total of 48. I hope this makes more 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 Again! -- Judy "Arvin Meyer [MVP]" wrote: This is aircode, but if I understand your question correctly, you need another query to return ALL weeks instead of what it presently does. Then: If Me.Current_Deparyment_Name = "Reserves" Then =DSum("[WeeksService]","Service Record Query2","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") Else =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") End If Notice a different query: "Service Record Query2" which will return all weeks instead of specific ones. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy . |
#7
|
|||
|
|||
Changing existing code to add "IF Statement"
"Arvin Meyer [MVP]" wrote in message ... Yes, you just need a new query which shows ALL departments. Department Name doesn't need to be a part of the query. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... Arvin, Thanks for responding. After rereading my post, I find that I wasn't very clear. I'll try again. The existing code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I do want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL Department Name IF the Current Department Name is equal to "Reserves". Example: Department Name Weeks Service Millwright 35 weeks Pipefitter 10 weeks Millwright (Current Deptment Name) 3 weeks The existing code adds all the Weeks Service where the Department Name is equal to the Current Department (Millwright) for a total of 38. In the same example, if the Current Department were "Reserves", I need code that will add ALL of the Weeks Service of ALL Department Name to give me a total of 48. I hope this makes more 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 Again! -- Judy "Arvin Meyer [MVP]" wrote: This is aircode, but if I understand your question correctly, you need another query to return ALL weeks instead of what it presently does. Then: If Me.Current_Deparyment_Name = "Reserves" Then =DSum("[WeeksService]","Service Record Query2","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") Else =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") End If Notice a different query: "Service Record Query2" which will return all weeks instead of specific ones. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "judyb" wrote in message ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy . |
#8
|
|||
|
|||
Changing existing code to add "IF Statement"
uklmmùù
"judyb" a écrit dans le message de groupe de discussion : ... I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advance. -- Judy |
Thread Tools | |
Display Modes | |
|
|