Changing existing code to add "IF Statement"

[judyb]

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.

 

[Arvin Meyer [MVP]]

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.

 

[Rebecca Brayer]

ztuiolökujzhjklrdmncfgvbvjh ghhgfdghtgcfdf

 

[judyb]

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

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

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.

.

 

[Steve]

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
(e-mail address removed)

 

[Arvin Meyer [MVP]]

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

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

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

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.

.

 

[De Jager]

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

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

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

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


.

 

[joelgeraldine]

uklmmùù