Subquery problem on report

[John White]

I have text boxes in a report that I need to use for calculations to compare
sales goals with actual amounts. The actual amounts work fine, but I keep
receiving #name# errors on the subquery, which is drawing info from a table
called "tblSalesGoals". The sql statement that isn't working is:

=(SELECT [GoalAmount] FROM [tblSalesGoals] WHERE [Month] = "04/30/2006").
Each text box will have a different [Month] reference to limit the results
to specified months.

Any ideas?

Thank you!

John

 

[Allen Browne]

John, I take it you have placed this SQL statement into the Control Source
of a text box?

That won't work. Instead, use DLookup() in the ControlSource, like this:
=DLookup("GoalAmount", "tblSalesGoals", "[Month] = #04/30/2006#")

More info on DLookup():
http://allenbrowne.com/casu-07.html

Also, note the # delimiters around the literal date value in the Criteria.

 

[John White]

Thanks Allen.......... I had to enter the EmployeeID since that is a
variable item in the table and without it the result was blank.
=DLookUp("GoalAmount","tblSalesGoals","[Month] = #04/30/2006#" And
"[EmployeeID]=#John White#") - this works fine.

Can I draw the EmployeeID from the report automatically? The report is
named "rptOpportunitySummary, and each Saleperson has a header/footer. I'd
also like to use it in the report fotter and add all goals and performance
numbers for the team, but that's beyond the scope of this issue. The
formula you're helping with is in the salesperson summary.

By the way, your website tips are great - much better at explaining
structure than what I find elsewhere!

Thanks again!

John

John, I take it you have placed this SQL statement into the Control Source
of a text box?

That won't work. Instead, use DLookup() in the ControlSource, like this:
=DLookup("GoalAmount", "tblSalesGoals", "[Month] = #04/30/2006#")

More info on DLookup():
http://allenbrowne.com/casu-07.html

Also, note the # delimiters around the literal date value in the Criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have text boxes in a report that I need to use for calculations to
compare sales goals with actual amounts. The actual amounts work fine,
but I keep receiving #name# errors on the subquery, which is drawing info
from a table called "tblSalesGoals". The sql statement that isn't working
is:

=(SELECT [GoalAmount] FROM [tblSalesGoals] WHERE [Month] = "04/30/2006").
Each text box will have a different [Month] reference to limit the
results to specified months.

 

[Allen Browne]

Sure. You can concatenate the EmployeeID value from the report into the
Criteria argument:

=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""" & [EmployeeID &
""")")

You do use quotes for a Text type field. And since they are embedded in a
string that has quotes around it, they are doubled up. The result is
equivalent to:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""John White"")")

If EmployeeID is a number field, use:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] = " & [EmployeeID] &
")")

Sometimes on a report, this does not work unless there is actually a control
on the report named EmployeeID to refer to. (Forms never have that issue.)

Feedback appreciated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.......... I had to enter the EmployeeID since that is a
variable item in the table and without it the result was blank.
=DLookUp("GoalAmount","tblSalesGoals","[Month] = #04/30/2006#" And
"[EmployeeID]=#John White#") - this works fine.

Can I draw the EmployeeID from the report automatically? The report is
named "rptOpportunitySummary, and each Saleperson has a header/footer.
I'd also like to use it in the report fotter and add all goals and
performance numbers for the team, but that's beyond the scope of this
issue. The formula you're helping with is in the salesperson summary.

By the way, your website tips are great - much better at explaining
structure than what I find elsewhere!

Thanks again!

John

John, I take it you have placed this SQL statement into the Control
Source of a text box?

That won't work. Instead, use DLookup() in the ControlSource, like this:
=DLookup("GoalAmount", "tblSalesGoals", "[Month] = #04/30/2006#")

More info on DLookup():
http://allenbrowne.com/casu-07.html

Also, note the # delimiters around the literal date value in the
Criteria.

I have text boxes in a report that I need to use for calculations to
compare sales goals with actual amounts. The actual amounts work fine,
but I keep receiving #name# errors on the subquery, which is drawing info
from a table called "tblSalesGoals". The sql statement that isn't
working is:

=(SELECT [GoalAmount] FROM [tblSalesGoals] WHERE [Month] =
"04/30/2006"). Each text box will have a different [Month] reference to
limit the results to specified months.

 

[John White]

Thanks Allen - It worked perfectly!

The final two steps are giving me fits - I've been trying different
expressions for a couple of hours and thought I had it, but not quite. I'm
trying to total actuals, but one of the parts is the result of a calculated
field:

Field Name: Proposal
Expression: =Choose([ProposalStatus],"None","Pending","Approved")

Expression using this field: =Sum(IIf([ApprovalDueDate] Between #4/1/2006#
AND #4/30/2006# And [EmployeeID]="" & [EmployeeID] & "" AND
[ProposalStatus]="Approved",[ProjectedEstimatedValue],0))

In other words, I'm attempting to total all values
([ProjectedEstimatedValue]) for a given date range (([ApprovalDueDate]
Between #4/1/2006# And #4/30/2006#) for an employee ([EmployeeID]="" &
[EmployeeID] & "" ) where the Proposal Status = Approved

After that, I need to derive the % to goal based on the result of this
expression against the result of =DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] = " & [EmployeeID] & ")") which
you gave me below.

DO YOU TAKE DONATIONS? 8~)

Thanks again!

John

Sure. You can concatenate the EmployeeID value from the report into the
Criteria argument:

=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""" & [EmployeeID &
""")")

You do use quotes for a Text type field. And since they are embedded in a
string that has quotes around it, they are doubled up. The result is
equivalent to:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""John White"")")

If EmployeeID is a number field, use:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] = " & [EmployeeID] &
")")

Sometimes on a report, this does not work unless there is actually a
control on the report named EmployeeID to refer to. (Forms never have that
issue.)

Feedback appreciated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.......... I had to enter the EmployeeID since that is a
variable item in the table and without it the result was blank.
=DLookUp("GoalAmount","tblSalesGoals","[Month] = #04/30/2006#" And
"[EmployeeID]=#John White#") - this works fine.

Can I draw the EmployeeID from the report automatically? The report is
named "rptOpportunitySummary, and each Saleperson has a header/footer.
I'd also like to use it in the report fotter and add all goals and
performance numbers for the team, but that's beyond the scope of this
issue. The formula you're helping with is in the salesperson summary.

By the way, your website tips are great - much better at explaining
structure than what I find elsewhere!

Thanks again!

John

John, I take it you have placed this SQL statement into the Control
Source of a text box?

That won't work. Instead, use DLookup() in the ControlSource, like this:
=DLookup("GoalAmount", "tblSalesGoals", "[Month] = #04/30/2006#")

More info on DLookup():
http://allenbrowne.com/casu-07.html

Also, note the # delimiters around the literal date value in the
Criteria.

I have text boxes in a report that I need to use for calculations to
compare sales goals with actual amounts. The actual amounts work fine,
but I keep receiving #name# errors on the subquery, which is drawing
info from a table called "tblSalesGoals". The sql statement that isn't
working is:

=(SELECT [GoalAmount] FROM [tblSalesGoals] WHERE [Month] =
"04/30/2006"). Each text box will have a different [Month] reference to
limit the results to specified months.

 

[Allen Browne]

Firstly, I assume that ProposalStatus is a Numeric field, and that Approved
is the value 2. The expression should use the number 2, since that will
match the value in the field, i.e.
=Sum(IIf(... AND [ProposalStatus] = 2 ...

Next, I don't follow the EmployeeID part of this expression. The Sum() is
evaluated for each record. Whatever EmployeeID is in that record, it will
always be the same as the EmployeeID field in that record, so it seems to me
that the:
EmployeeID = EmployeeID
part will always be true (except for records where EmployeeID is null.)

Or perhaps this expression goes into an EmployeeID group footer, and you are
trying to refer to this employee? If so, try renaming the text box in the
group footer to something like txtEmpID, and then compare [EmployeeID] to
[txtEmpID].

If you are really stuck on that, it would be possible to sum the records
directly from the table with DSum().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen - It worked perfectly!

The final two steps are giving me fits - I've been trying different
expressions for a couple of hours and thought I had it, but not quite.
I'm trying to total actuals, but one of the parts is the result of a
calculated field:

Field Name: Proposal
Expression: =Choose([ProposalStatus],"None","Pending","Approved")

Expression using this field: =Sum(IIf([ApprovalDueDate] Between #4/1/2006#
AND #4/30/2006# And [EmployeeID]="" & [EmployeeID] & "" AND
[ProposalStatus]="Approved",[ProjectedEstimatedValue],0))

In other words, I'm attempting to total all values
([ProjectedEstimatedValue]) for a given date range (([ApprovalDueDate]
Between #4/1/2006# And #4/30/2006#) for an employee ([EmployeeID]="" &
[EmployeeID] & "" ) where the Proposal Status = Approved

After that, I need to derive the % to goal based on the result of this
expression against the result of =DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] = " & [EmployeeID] & ")")
which you gave me below.

DO YOU TAKE DONATIONS? 8~)

Thanks again!

John

Sure. You can concatenate the EmployeeID value from the report into the
Criteria argument:

=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""" & [EmployeeID &
""")")

You do use quotes for a Text type field. And since they are embedded in a
string that has quotes around it, they are doubled up. The result is
equivalent to:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] =""John White"")")

If EmployeeID is a number field, use:
=DLookUp("GoalAmount", "tblSalesGoals",
"([Month] = #04/30/2006#) And ([EmployeeID] = " & [EmployeeID] &
")")

Sometimes on a report, this does not work unless there is actually a
control on the report named EmployeeID to refer to. (Forms never have
that issue.)

Feedback appreciated.

Thanks Allen.......... I had to enter the EmployeeID since that is a
variable item in the table and without it the result was blank.
=DLookUp("GoalAmount","tblSalesGoals","[Month] = #04/30/2006#" And
"[EmployeeID]=#John White#") - this works fine.

Can I draw the EmployeeID from the report automatically? The report is
named "rptOpportunitySummary, and each Saleperson has a header/footer.
I'd also like to use it in the report fotter and add all goals and
performance numbers for the team, but that's beyond the scope of this
issue. The formula you're helping with is in the salesperson summary.

By the way, your website tips are great - much better at explaining
structure than what I find elsewhere!

Thanks again!

John
John, I take it you have placed this SQL statement into the Control
Source of a text box?

That won't work. Instead, use DLookup() in the ControlSource, like
this:
=DLookup("GoalAmount", "tblSalesGoals", "[Month] = #04/30/2006#")

More info on DLookup():
http://allenbrowne.com/casu-07.html

Also, note the # delimiters around the literal date value in the
Criteria.

I have text boxes in a report that I need to use for calculations to
compare sales goals with actual amounts. The actual amounts work fine,
but I keep receiving #name# errors on the subquery, which is drawing
info from a table called "tblSalesGoals". The sql statement that isn't
working is:

=(SELECT [GoalAmount] FROM [tblSalesGoals] WHERE [Month] =
"04/30/2006"). Each text box will have a different [Month] reference
to limit the results to specified months.