Sub report message

[Aria]

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

 

[Aria]

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.

The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.

The is named "Site Employees".

The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
What control is it referencing?
What field is it mentioning?
Where did you add these?
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

What control is it referencing?

I assume it was referring to the IIF statement that you wanted me to place
in the text box.

How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
What control is it referencing?
What field is it mentioning?
Where did you add these?
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

What control is it referencing?

I assume it was referring to the IIF statement that you wanted me to place
in the text box.

How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€

What control is it referencing?

and “No such field in the field listâ€.

What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.

Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.

How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.

Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.

Why is this incorrect?

Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.

key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[KARL DEWEY]

tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.

How can I include all staff in this sub report whether they were issued a

key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could
use some help…

 

[Aria]

That's what I did first. I added the employee table (FirstName, LastName and
Active = True). It was OK as an inner join (Option #1) but you said to use a
left join (Option #2) which is what I tried to do. That's when I received the
message about ambiguous joins. Then I tried the sub query but the message was
the same.
--
Aria W.

tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.

How can I include all staff in this sub report whether they were issued a
key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

:

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

FoodService: IIF[DeptName] = “Food Serviceâ€, [KeyCode] = “No additional info
needed.â€, [KeyCode]

I tried this in the criteria line:
Reports![rptSiteEmployees]![srptDepts]![txtDeptName]


I know this is wrong because I received a parameter value pop-up. The key
assignments sub report doesn't have a field named [DeptName]. I also tried
adding the juction table tblDeptEmps, as well as tblDept to the query. When I
looked at it in datasheet view, only the column headings were visible. Could

 

[KARL DEWEY]

Post the SQL with the left join that is giving you the ambiguous error message.

That's what I did first. I added the employee table (FirstName, LastName and
Active = True). It was OK as an inner join (Option #1) but you said to use a
left join (Option #2) which is what I tried to do. That's when I received the
message about ambiguous joins. Then I tried the sub query but the message was
the same.
--
Aria W.

So I tried to create a sub query that includes all records from

tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.


:

How can I include all staff in this sub report whether they were issued a
key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

:

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria
contained in one sub report to print a message in the body of another sub
report? The report query doesn’t contain this info.
I’ve tried a lot of different things including the following:

 

[Aria]

Left Join SQL (after adding tblEmployees):

SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode,
tblKeyAssignments.DateRtrnd, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.Active
FROM tblKeys INNER JOIN (tblEmployees LEFT JOIN tblKeyAssignments ON
tblEmployees.EmpID = tblKeyAssignments.EmpID) ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblEmployees.Active)=True))
ORDER BY tblKeys.KeyCode;

--
Aria W.

Post the SQL with the left join that is giving you the ambiguous error message.

That's what I did first. I added the employee table (FirstName, LastName and
Active = True). It was OK as an inner join (Option #1) but you said to use a
left join (Option #2) which is what I tried to do. That's when I received the
message about ambiguous joins. Then I tried the sub query but the message was
the same.
--
Aria W.

So I tried to create a sub query that includes all records from
tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.


:

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.


:

How can I include all staff in this sub report whether they were issued a
key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

:

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?



--
Aria W.


:

I cannot follow what you are trying to do as you say there is no field
named[DeptName] but you put it in the IIF statement.

By the way the IIF statement is wrong in that you have two test expressions
and only one result. What is the IIF statement suppose to to be checking?
What is suppose to be the result if true or if false?

:

I need to modify the output of one of my reports. This particular report
contains 3 sub reports. How can I reference information using criteria

 

[KARL DEWEY]

Try this --
FROM (tblEmployees LEFT JOIN tblKeyAssignments ON tblEmployees.EmpID =
tblKeyAssignments.EmpID) LEFT JOIN tblKeys ON tblKeys.KeyID =
tblKeyAssignments.KeyID

Left Join SQL (after adding tblEmployees):

SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode,
tblKeyAssignments.DateRtrnd, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.Active
FROM tblKeys INNER JOIN (tblEmployees LEFT JOIN tblKeyAssignments ON
tblEmployees.EmpID = tblKeyAssignments.EmpID) ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblEmployees.Active)=True))
ORDER BY tblKeys.KeyCode;

--
Aria W.

Post the SQL with the left join that is giving you the ambiguous error message.

That's what I did first. I added the employee table (FirstName, LastName and
Active = True). It was OK as an inner join (Option #1) but you said to use a
left join (Option #2) which is what I tried to do. That's when I received the
message about ambiguous joins. Then I tried the sub query but the message was
the same.
--
Aria W.


:

So I tried to create a sub query that includes all records from
tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.


:

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.


:

How can I include all staff in this sub report whether they were issued a
key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

:

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd
FROM tblKeys INNER JOIN tblKeyAssignments ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null))
ORDER BY tblKeys.KeyCode;

Do you have any advice on how I can change this in order to do what I want?

 

[Aria]

Great! That worked and also got rid of the invalid control source, field not
in list and the sorting & grouping field name error messages.

I added the IIF statement:

Food Service: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I checked in datasheet view and I see all staff. The Food Service is the
only dept. that has the "no additional" message listed. Everyone else has the
key code listed. Perfect!

I then added the IIF statement to the text box:

=IIf([DeptName]="Food Service","No additional info needed.",[KeyCode])

Originally, I didn't have the = sign in front but I received a message that
said it needs to be there. When I changed to report view, I didn't see any
message for the Food Service dept. What did I forget?

--
Aria W.

Try this --
FROM (tblEmployees LEFT JOIN tblKeyAssignments ON tblEmployees.EmpID =
tblKeyAssignments.EmpID) LEFT JOIN tblKeys ON tblKeys.KeyID =
tblKeyAssignments.KeyID

Left Join SQL (after adding tblEmployees):

SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode,
tblKeyAssignments.DateRtrnd, tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.Active
FROM tblKeys INNER JOIN (tblEmployees LEFT JOIN tblKeyAssignments ON
tblEmployees.EmpID = tblKeyAssignments.EmpID) ON tblKeys.KeyID =
tblKeyAssignments.KeyID
WHERE (((tblKeyAssignments.DateRtrnd) Is Null) AND
((tblEmployees.Active)=True))
ORDER BY tblKeys.KeyCode;

--
Aria W.

Post the SQL with the left join that is giving you the ambiguous error message.

:

That's what I did first. I added the employee table (FirstName, LastName and
Active = True). It was OK as an inner join (Option #1) but you said to use a
left join (Option #2) which is what I tried to do. That's when I received the
message about ambiguous joins. Then I tried the sub query but the message was
the same.
--
Aria W.


:

So I tried to create a sub query that includes all records from
tblEmployees and only those records from tblKeyAssignments where the join
fields are equal. They are linked by employee ID. Is this correct?
No 'subquery' but the employee table left join. Of course include criteria
like Active.


:

It must be me. I don't understand. I received an error message that says,
"The SQL could not be executed because it contains ambiguous outer joins. To
fornce one of the joins to be performed first, create a seperate query that
performs the first join and then include that query in your SQL statement."

This is the same message I received yesterday with the other query (the one
that inculded DeptName). So I tried to create a sub query that includes all
records from tblEmployees and only those records from tblKeyAssignments where
the join fields are equal. They are linked by employee ID. Is this correct?

I then tried to add this query to the grid for the Keys sub report and tried
to view the results in the datasheet. I receive the exact same message about
ambiguous joins. Where am I missing the boat?
--
Aria W.


:

How can I include all staff in this sub report whether they were issued a
key or not?
Left join employee table to issue table. If you are using criteria then
include 'OR Null' so as to pull employees not issued key.

:

Before I posted this was my original IIF statement:
FoodService: IIf([DeptName]="Food Service","No additional info
needed.",[KeyCode])

I guess I just wasn’t paying attention when I posted the other statement.
Thanks for the clarification. But I want to go back to something you posted a
few days ago.

How do you expect to test this if you do not put a record that will >match the criteria?

This is the question that I kept thinking about because I just couldn’t see
that it should be this difficult. Something is missing. Based on the things
that I tried yesterday, I am coming to the sinking realization that I may
*not* have a record that matches. There are a few things that I noticed and
will try to explain as best I can.

This is what I looked at:
1.I checked the main report query to see if there was a problem. I see what
I expect to see. All site staff are listed including all of the Food Service
dept. (EmpID and name).
2.I then checked the sub report query again. As I previously posted, there
was an oddity here in that there were some master keys listed under Food
Service and “No additional info needed.â€
3.Since I shouldn’t see a master key listed with Food Service, I added
additional fields ([FullName] and [DateIssued]) to the query so I could see
what was happening.
4.I then switched to datasheet view.
5.I focused on 2 master keys listed under Food Service.

Problem:
In the main report query, all is well. Both employees have the correct
employee ID. In the sub query, both employees have employee IDs that are not
their own and the keys associated with the incorrect ID.

I took a closer look at the sub report query (qryKeyAssignmentsSubreport)
and noted the following:
1.All staff are not listed in this sub query.
2.Wrong employee ID, Key and Key Code listed for staff who *never* received
the key shown. Some are Food Service who, if anything, should have a record
that is blank.
3.A single staff member may be listed multiple times with a key code but
different employee IDs and date issued for that key.

I tried changing the join but received a message about ambiguous outer
joins. I think if I could fix what’s wrong I would be able to get the message
to appear. The problem is I don’t know how to fix this. How can I include all
staff in this sub report whether they were issued a key or not? I realize
this is a different question from my original. I can repost if you’d rather.

--
Aria W.


:

Why is this incorrect?
Your puncuation -
Yours --
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

Mine --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

IIF must be followed by ( and the statement end with ) to be correct.

You need to put this in leu of the field [KeyCode] --
Key Code: IIF([DeptName] = "Food Service", "No additional info needed.",
[KeyCode])

This creates an alias with the name 'Key Code' to used instead of
'KeyCode'.


:

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

Why is this incorrect? I want the statement, "No additional info needed", to
appear in the key code section of the Site Employee report. If the employee
is *not* a member of the Food Service dept. then I want to see all the codes
for the keys that have been assigned to them. The Food Service dept. is
unique in that they are not assigned keys through our office. Their keys are
assigned through our district office. The layout of their work area is such
that they do not require keys to any other part of the school or their
building.

Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )
When I received the error message stating "No such field in the field list",
I added DeptID and DeptName to just to see what would happen.

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?
I assume it was referring to the IIF statement that you wanted me to place
in the text box.

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

I think I need to clarify the situation here. The report is already complete
and I have started using it extensively within the last month. The report
list *every* employee at our school by name, classification, title (admin.,
teacher, attendance, etc.), dept. and key code (keys that have been
assigned). There are other reports that track distict, substitute and
community personnel.

The Food Service dept. is the only dept. on campus where the key code will
not apply. So because things happen rapidly, I am looking for a way to cut
down on the cross referencing that needs to be done. This is a waste of time
in regards to that dept. At the beginning and end of the year, 95% of the
employees will not have a key code listed because they have either turned in
their keys or haven't picked them up yet. Food Service staff are currently
listed in the report but the key code is null.

I didn't mention master keys because I didn't think it really mattered.
Master keys are employee specific and afford all access per campus to every
classroom and office. I have a list of every site and district employee who
has been assigned a master key. There aren't any Food Service staff who have
been assigned a master key because this does not apply to their situation.
Which is why I noted that the master key should not have Food Service as a
dept. I'm sorry for the confusion. I hope this makes things a little clearer.

--
Aria W.


:

A couple of things.
Your IIF statement is wrong --
IIF([DeptName] = "Food Service", "No additional info needed.", [KeyCode] )

I receive the following error messages, “This control has an invalid control sourceâ€
What control is it referencing?

and “No such field in the field listâ€.
What field is it mentioning?

I added DeptID and DeptName just to see if that would help but it didn’t.
Where did you add these?

The problem with this is that there isn't any employee who currently has been assigned a master key that works in Food Service so I don't believe I should see this.
How do you expect to test this if you do not put a record that will match
the criteria?
What is this 'master key' you are talking about? This is something totally
new to the equation.

:

OK, I’m trying to follow you. Based on what I think you’re saying, I did the
following:

1.Added tblDeptEmps and tblDepts to the main report
query(qrySiteEmployeeReport).

2.Added qrySiteEmployeeReport to qryKeyAssignmentsSubReport on srptKeys.

3.I added the IIF statement:
IIF[DeptName] = "Food Service", "No additional info needed.", [KeyCode]

I added [KeyCode] because I want to see the key code for every other
employee if they are not part of the Food Service dept.

I’m still having trouble with this. I receive the following error messages,
“This control has an invalid control source†and “No such field in the field
listâ€. I added DeptID and DeptName just to see if that would help but it
didn’t. The other thing that I see is that when I go to datasheet view to
see how things are working I see a master key with the Food Service heading
and the message I want displayed. The problem with this is that there isn't
any employee who currently has been assigned a master key that works in Food
Service so I don't believe I should see this. I must not be following your
directions well enough.

--
Aria W.


:

I think you easiest method would be to include in the Dept in the query for
the main and then in the Key Assignments query join the main query.

Then in the Key Assignments subreport add a text box without a label and
source -- IIF[DeptName] = “Food Serviceâ€, “No additional info needed.â€, "")

Also have background and border for text box transparent and special effect
as flat. It will only show when DeptName equals Food Service.

:

I'm sorry about that. I meant to say the report is named "Site Employees".
The link between the all sub reports and the parent is EmpID. This is the
employee ID field.
--
Aria W.


:

The is named "Site Employees".
The sentence above is missing some data.

What is the field used for the Master/Child links between the main form and
the subforms?

:

Hi Karl,
What I am *trying to do is have a message on the Key Assignments subform
that says, "No additional info needed." but only if the Dept. name is "Food
Service". The is named "Site Employees". It doesn't contain any of the fields
I need. The 3 subreports are "Titles", "Depts" and "Key Assignments".
Here are the subreport SQL:
Depts:
SELECT tblDeptsEmps.EmpID, tblDepts.DeptName
FROM tblDepts INNER JOIN tblDeptsEmps ON tblDepts.DeptID = tblDeptsEmps.DeptID
ORDER BY tblDepts.DeptName;

Key Assignments:
SELECT tblKeyAssignments.EmpID, tblKeys.KeyCode, tblKeyAssignments.DateRtrnd