DLookup in a grouped footer

  • Thread starter Thread starter Ann
  • Start date Start date
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?
 
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...
 
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...
 
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...
 
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...
 
Back
Top