DLookup Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
 
It doesnt work with the brackets either.
=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls Query]","Agent
Name = ' " & [Agent Name] & " ' ")
Click to expand...
Brent said:
I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
Click to expand...
 
Brent said:
It doesnt work with the brackets either.
=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","Agent
Name = ' " & [Agent Name] & " ' ")
Click to expand...
Click to expand...

Try this:

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = ' " & Me![Agent Name] & " ' ")

Tom Lake
 
I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
Click to expand...

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
 
Thanks for the suggestions, but it is still coming up with the #Error on my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

fredg said:
I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
Click to expand...

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
 
What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query] to your
report's recordsource joining the Agent Name fields? This would allow you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Brent said:
Thanks for the suggestions, but it is still coming up with the #Error on my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

fredg said:
I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
Click to expand...

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
 
Thanks for the suggestions, but it is still coming up with the #Error on my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

fredg said:
I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.
Click to expand...

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...

What Me?
Both methods of writing the DLookUp() Where clause work for me, as
written.

Are you sure the query is returning a record for the selected agent
name.

Now you mention Me.
Where are you placing this DLookUp?
In an unbound control's control source?
You cannot use the Me keyword in an Access control. It is a VBA word,
and can only be used within a form or report objects code, not even in
a module.

I would suggest you copy your query and change the field and query
name to a shorter version (without spaces this time) and see if you
can get that to work. Long field or table names with spaces is not a
good habit to get into.
Also, searching for a record using a name field is fraught with
danger. Which record will be returned if you have 2 agents named John
Brown. Use an AgentID field as a search field. It's safer.

If you still are having a problem, copy and paste your exact
expression and control name into a message.
 
Okay. I have a report grouped by Agent and sorted by date. I cannot put my
query [Agent Setter ACD Calls Query] (which is now named
[ARLAgentSetterACDQuery]) into my record source because I have a different
query set as my record source, unless I can have two queries as a record
source, but I didn't know this was possible. I cannot add my data from
[ARLAgentSetterACDQuery] in my record source query because it skews the rest
of my data because of the way it is recorded. It almost works like this:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'")

But it is only giving me the first record for each agent. I need it by date
also. For Instance, Each agent has 5 days of data, well it is showing the
first days data for every day. I tried to add: And "[Date]='" & [Date] &
"'"), but it does the same thing.

Thanks so much for the help. I have done plenty of simple queries and
reports in Access, but this one is much more complicated than I have dealt
with.

-Brent


Duane Hookom said:
What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query] to your
report's recordsource joining the Agent Name fields? This would allow you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Brent said:
Thanks for the suggestions, but it is still coming up with the #Error on my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

fredg said:
On Thu, 14 Oct 2004 13:15:05 -0700, Brent wrote:

I am using a DLookup Function on my reports. It works fine until I add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
Click to expand...
 
I missed any earlier mention of this [Date] stuff. The delimiter for dates
is "#". I would try my earlier suggestion and join on Date and Agent. If
that doesn't work then try:
=DLookUp("[SumOfACD Calls Received]",
"[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] &
"' AND [DateField]=#" & [DateField] & "#)
I added carriage returns for display. Substitute your date field names for
the ones above.

--
Duane Hookom
MS Access MVP
--

Brent said:
Okay. I have a report grouped by Agent and sorted by date. I cannot put my
query [Agent Setter ACD Calls Query] (which is now named
[ARLAgentSetterACDQuery]) into my record source because I have a different
query set as my record source, unless I can have two queries as a record
source, but I didn't know this was possible. I cannot add my data from
[ARLAgentSetterACDQuery] in my record source query because it skews the rest
of my data because of the way it is recorded. It almost works like this:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'")

But it is only giving me the first record for each agent. I need it by date
also. For Instance, Each agent has 5 days of data, well it is showing the
first days data for every day. I tried to add: And "[Date]='" & [Date] &
"'"), but it does the same thing.

Thanks so much for the help. I have done plenty of simple queries and
reports in Access, but this one is much more complicated than I have dealt
with.

-Brent


Duane Hookom said:
What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query] to your
report's recordsource joining the Agent Name fields? This would allow you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Brent said:
Thanks for the suggestions, but it is still coming up with the #Error
Click to expand...
on
my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

:

On Thu, 14 Oct 2004 13:15:05 -0700, Brent wrote:

I am using a DLookup Function on my reports. It works fine until
Click to expand...
I
add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure I understood your earlier suggestion. Can I add a field from a
different query than the one that is already my record source? When I used:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'" AND [Date]=#" & [Date] & "#)

it says the expression you entered has an invalid date value???

Thank you again for all your help. Sorry if I am not understanding.

Duane Hookom said:
I missed any earlier mention of this [Date] stuff. The delimiter for dates
is "#". I would try my earlier suggestion and join on Date and Agent. If
that doesn't work then try:
=DLookUp("[SumOfACD Calls Received]",
"[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] &
"' AND [DateField]=#" & [DateField] & "#)
I added carriage returns for display. Substitute your date field names for
the ones above.

--
Duane Hookom
MS Access MVP
--

Brent said:
Okay. I have a report grouped by Agent and sorted by date. I cannot put my
query [Agent Setter ACD Calls Query] (which is now named
[ARLAgentSetterACDQuery]) into my record source because I have a different
query set as my record source, unless I can have two queries as a record
source, but I didn't know this was possible. I cannot add my data from
[ARLAgentSetterACDQuery] in my record source query because it skews the rest
of my data because of the way it is recorded. It almost works like this:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'")

But it is only giving me the first record for each agent. I need it by date
also. For Instance, Each agent has 5 days of data, well it is showing the
first days data for every day. I tried to add: And "[Date]='" & [Date] &
"'"), but it does the same thing.

Thanks so much for the help. I have done plenty of simple queries and
reports in Access, but this one is much more complicated than I have dealt
with.

-Brent


Duane Hookom said:
What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query] to your
report's recordsource joining the Agent Name fields? This would allow you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Thanks for the suggestions, but it is still coming up with the #Error on
my
reports. When I used the Me it asked me for parameter values for Me. I
don't know why this is being so difficult.

:

On Thu, 14 Oct 2004 13:15:05 -0700, Brent wrote:

I am using a DLookup Function on my reports. It works fine until I
add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls
Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
Click to expand...
 
I see I missed the last double-quote plus you added an extra one.
=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] & "' AND [Date]=#" & [Date] & "#")

My earlier suggestion was to add [ARLAgentSetterACDQuery] to your reports
record source and join the Agent Name and Date fields.
--
Duane Hookom
MS Access MVP


Brent said:
I'm not sure I understood your earlier suggestion. Can I add a field from a
different query than the one that is already my record source? When I used:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'" AND [Date]=#" & [Date] & "#)

it says the expression you entered has an invalid date value???

Thank you again for all your help. Sorry if I am not understanding.

Duane Hookom said:
I missed any earlier mention of this [Date] stuff. The delimiter for dates
is "#". I would try my earlier suggestion and join on Date and Agent. If
that doesn't work then try:
=DLookUp("[SumOfACD Calls Received]",
"[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] &
"' AND [DateField]=#" & [DateField] & "#)
I added carriage returns for display. Substitute your date field names for
the ones above.

--
Duane Hookom
MS Access MVP
--

Brent said:
Okay. I have a report grouped by Agent and sorted by date. I cannot
Click to expand...
put
my
query [Agent Setter ACD Calls Query] (which is now named
[ARLAgentSetterACDQuery]) into my record source because I have a different
query set as my record source, unless I can have two queries as a record
source, but I didn't know this was possible. I cannot add my data from
[ARLAgentSetterACDQuery] in my record source query because it skews
Click to expand...
the
rest
of my data because of the way it is recorded. It almost works like this:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'")

But it is only giving me the first record for each agent. I need it
Click to expand...
by
date
also. For Instance, Each agent has 5 days of data, well it is showing the
first days data for every day. I tried to add: And "[Date]='" & [Date] &
"'"), but it does the same thing.

Thanks so much for the help. I have done plenty of simple queries and
reports in Access, but this one is much more complicated than I have dealt
with.

-Brent


:

What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query]
Click to expand...
to
your
report's recordsource joining the Agent Name fields? This would
Click to expand...
allow
you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Thanks for the suggestions, but it is still coming up with the
Click to expand...
#Error
on
my
reports. When I used the Me it asked me for parameter values for
Click to expand...
Me.
I
don't know why this is being so difficult.

:

On Thu, 14 Oct 2004 13:15:05 -0700, Brent wrote:

I am using a DLookup Function on my reports. It works fine
Click to expand...
until
I
add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls
Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST
Click to expand...
enclose
it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much. I think you just solved all my problems.

Duane Hookom said:
I see I missed the last double-quote plus you added an extra one.
=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] & "' AND [Date]=#" & [Date] & "#")

My earlier suggestion was to add [ARLAgentSetterACDQuery] to your reports
record source and join the Agent Name and Date fields.
--
Duane Hookom
MS Access MVP


Brent said:
I'm not sure I understood your earlier suggestion. Can I add a field from a
different query than the one that is already my record source? When I used:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'" AND [Date]=#" & [Date] & "#)

it says the expression you entered has an invalid date value???

Thank you again for all your help. Sorry if I am not understanding.

Duane Hookom said:
I missed any earlier mention of this [Date] stuff. The delimiter for dates
is "#". I would try my earlier suggestion and join on Date and Agent. If
that doesn't work then try:
=DLookUp("[SumOfACD Calls Received]",
"[ARLAgentSetterACDQuery]",
"[Agent Name]='" & [Agent Name] &
"' AND [DateField]=#" & [DateField] & "#)
I added carriage returns for display. Substitute your date field names for
the ones above.

--
Duane Hookom
MS Access MVP
--

Okay. I have a report grouped by Agent and sorted by date. I cannot put
my
query [Agent Setter ACD Calls Query] (which is now named
[ARLAgentSetterACDQuery]) into my record source because I have a different
query set as my record source, unless I can have two queries as a record
source, but I didn't know this was possible. I cannot add my data from
[ARLAgentSetterACDQuery] in my record source query because it skews the
rest
of my data because of the way it is recorded. It almost works like this:

=DLookUp("[SumOfACD Calls Received]","[ARLAgentSetterACDQuery]","[Agent
Name]='" & [Agent Name] & "'")

But it is only giving me the first record for each agent. I need it by
date
also. For Instance, Each agent has 5 days of data, well it is showing the
first days data for every day. I tried to add: And "[Date]='" & [Date] &
"'"), but it does the same thing.

Thanks so much for the help. I have done plenty of simple queries and
reports in Access, but this one is much more complicated than I have dealt
with.

-Brent


:

What section is the control? What is your current expression?
Is there a reason why you don't add [Agent Setter ACD Calls Query] to
your
report's recordsource joining the Agent Name fields? This would allow
you to
add the SumOfACD Calls Received field to the report.

BTW: you should consider finding a better naming convention.

--
Duane Hookom
MS Access MVP


Thanks for the suggestions, but it is still coming up with the #Error
on
my
reports. When I used the Me it asked me for parameter values for Me.
I
don't know why this is being so difficult.

:

On Thu, 14 Oct 2004 13:15:05 -0700, Brent wrote:

I am using a DLookup Function on my reports. It works fine until
I
add my
criteria. I need it by Agent. Right now I have:

=DLookUp("SumOfACD Calls Received","Agent Setter ACD Calls
Query","Agent
Name = ' " & [Agent Name] & " ' ")

I'm getting an error on my reports. Any help would be great.

1) Remove the spaces between the single and double quotes in the
Where clause.

2) You have a space in the 'Agent Name' field, so you MUST enclose
it
within brackets (in both instances, as well as the other field and
table names with spaces).

=DLookUp("[SumOfACD Calls Received]","[Agent Setter ACD Calls
Query]","[Agent Name] = '" & [Agent Name] & "'")

Also, if there is any chance that the [Agent Name] value will
contain
a single quote (i.e. O'Brien), use:

"[Agent Name] =" & Chr(34) & [Agent Name] & Chr(34) & "")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top