DLookup in a grouped footer

A

Ann

I've created a table called tblNumberOfSurveys from an append query that has
the following fields: txtCourseTitle, lngNumberOfSurveys and Date. I need to
use this in a DLookup in a report I have created.

The report is rptQuestionsOneAndFive that has calculations in the
txtCourseTitleFooter. Since these are by the title of each course I am
grouping by txtCourseTitle. What I've done is add the following code to a
text box within the footer:
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Forms!rptQuestionsOneAndFive!txtCourseTitle). When I run the report I
receive the following on my DLookup, #Name?. I have check everything to make
sure they are spelled correctly. There are only three titles in my
tblNumberOfSurveys and they are the same three titles that appear on my
report. I'm a beginning in VB so I can't figure out why I don't get the
lngNumberOfSurveys number when I run my report. Can anyone help me out?
 
A

Ann

Sorry, it's
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Reports!rptQuestionsOneAndFive!txtCourseTitle). I used Forms instead of Reports but now I get #Error.
Click to expand...

Ann said:
I've created a table called tblNumberOfSurveys from an append query that has
the following fields: txtCourseTitle, lngNumberOfSurveys and Date. I need to
use this in a DLookup in a report I have created.

The report is rptQuestionsOneAndFive that has calculations in the
txtCourseTitleFooter. Since these are by the title of each course I am
grouping by txtCourseTitle. What I've done is add the following code to a
text box within the footer:
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Forms!rptQuestionsOneAndFive!txtCourseTitle). When I run the report I
receive the following on my DLookup, #Name?. I have check everything to make
sure they are spelled correctly. There are only three titles in my
tblNumberOfSurveys and they are the same three titles that appear on my
report. I'm a beginning in VB so I can't figure out why I don't get the
lngNumberOfSurveys number when I run my report. Can anyone help me out?
Click to expand...
 
J

John Spencer

Assumption is that txtCourseTitle is a text field. If so you need some quotes
added to the DLookup

=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = """ &
Reports!rptQuestionsOneAndFive!txtCourseTitle & """")

You could also use a single quote but that is more problematic since a course
title might contain an ' apostrophe in the title string.
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = '" &
Reports!rptQuestionsOneAndFive!txtCourseTitle & "'")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Sorry, it's
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Reports!rptQuestionsOneAndFive!txtCourseTitle). I used Forms instead of Reports but now I get #Error.
Click to expand...

Ann said:
I've created a table called tblNumberOfSurveys from an append query that has
the following fields: txtCourseTitle, lngNumberOfSurveys and Date. I need to
use this in a DLookup in a report I have created.

The report is rptQuestionsOneAndFive that has calculations in the
txtCourseTitleFooter. Since these are by the title of each course I am
grouping by txtCourseTitle. What I've done is add the following code to a
text box within the footer:
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Forms!rptQuestionsOneAndFive!txtCourseTitle). When I run the report I
receive the following on my DLookup, #Name?. I have check everything to make
sure they are spelled correctly. There are only three titles in my
tblNumberOfSurveys and they are the same three titles that appear on my
report. I'm a beginning in VB so I can't figure out why I don't get the
lngNumberOfSurveys number when I run my report. Can anyone help me out?
Click to expand...
Click to expand...
 
A

Ann

That worked great!! Thanks so much for your help. The quotes are pretty
close together and I was wondering are they all double quotes then? Why so
many? I know you need them around the entire last part but am I putting a
set of double quotes around double quotes just to get a single double quote?

John Spencer said:
Assumption is that txtCourseTitle is a text field. If so you need some quotes
added to the DLookup

=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = """ &
Reports!rptQuestionsOneAndFive!txtCourseTitle & """")

You could also use a single quote but that is more problematic since a course
title might contain an ' apostrophe in the title string.
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = '" &
Reports!rptQuestionsOneAndFive!txtCourseTitle & "'")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Sorry, it's
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Reports!rptQuestionsOneAndFive!txtCourseTitle). I used Forms instead of Reports but now I get #Error.
Click to expand...

Ann said:
I've created a table called tblNumberOfSurveys from an append query that has
the following fields: txtCourseTitle, lngNumberOfSurveys and Date. I need to
use this in a DLookup in a report I have created.

The report is rptQuestionsOneAndFive that has calculations in the
txtCourseTitleFooter. Since these are by the title of each course I am
grouping by txtCourseTitle. What I've done is add the following code to a
text box within the footer:
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Forms!rptQuestionsOneAndFive!txtCourseTitle). When I run the report I
receive the following on my DLookup, #Name?. I have check everything to make
sure they are spelled correctly. There are only three titles in my
tblNumberOfSurveys and they are the same three titles that appear on my
report. I'm a beginning in VB so I can't figure out why I don't get the
lngNumberOfSurveys number when I run my report. Can anyone help me out?
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Spencer

Yes they are all double quotes.

In order to get a double quote in a string, you need to have two quotes
immediately adjacent to each other.
So "" just opens and closes a string and you have a zero-length string
" "" " would be a string of space quote space
"""" would be a string of just a quote

" xxxx """ would be a string of space xxxx and a quote at the end.

Some people use Chr(34) to get the quote mark and some people Declare a constant

Const DQ as String = DQ

Then they build the string as follows to make it easier to understand
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " & DQ
& Reports!rptQuestionsOneAndFive!txtCourseTitle & DQ)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
That worked great!! Thanks so much for your help. The quotes are pretty
close together and I was wondering are they all double quotes then? Why so
many? I know you need them around the entire last part but am I putting a
set of double quotes around double quotes just to get a single double quote?

John Spencer said:
Assumption is that txtCourseTitle is a text field. If so you need some quotes
added to the DLookup

=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = """ &
Reports!rptQuestionsOneAndFive!txtCourseTitle & """")

You could also use a single quote but that is more problematic since a course
title might contain an ' apostrophe in the title string.
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = '" &
Reports!rptQuestionsOneAndFive!txtCourseTitle & "'")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Sorry, it's
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Reports!rptQuestionsOneAndFive!txtCourseTitle). I used Forms instead of Reports but now I get #Error.
:

I've created a table called tblNumberOfSurveys from an append query that has
the following fields: txtCourseTitle, lngNumberOfSurveys and Date. I need to
use this in a DLookup in a report I have created.

The report is rptQuestionsOneAndFive that has calculations in the
txtCourseTitleFooter. Since these are by the title of each course I am
grouping by txtCourseTitle. What I've done is add the following code to a
text box within the footer:
=DLookUp("[lngNumberOfSurveys]","tblNumberOfSurveys","txtCourseTitle = " &
Forms!rptQuestionsOneAndFive!txtCourseTitle). When I run the report I
receive the following on my DLookup, #Name?. I have check everything to make
sure they are spelled correctly. There are only three titles in my
tblNumberOfSurveys and they are the same three titles that appear on my
report. I'm a beginning in VB so I can't figure out why I don't get the
lngNumberOfSurveys number when I run my report. Can anyone help me out?
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Group Footer DLookUp 0
Duplicate Paramters 2
DLookup in Continuous form 0
Schedule Comparison Error - mismatch duration - "The duration you entered isn't supported for this field. type a duration in the standard format" 0
Multiple Criteria in Listbox 3
Run delete query instead of report... 4
Global layouts 0
Space at end of multi page list to accommodate new entries 1

Top