DLookup issue

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

Guest

I am getting the following error:
The specified field <field> could refer to more than one table listed in the
FROM clause of your SQL statement. (Error 3079)

I do not know where to findthe SQL statement to correct this. I only have
one table with the name in the dLookup coding that I am using to view the
comment text typed into a form and the course status chosen by the teacher
for a particular student in a particular course. The results are then shown
on a 1 page report. The coding I used for the dLookups are as follows:
in the field on the report called comment text:

=DLookUp([Comments],[All Students in TMS 2004 2005]!Comments," & Tables!
[All Students in TMS 2004 2005]![Comments]")

for the Course status field:

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus," &
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Please help me---I am getting totally frustrated.
Thanks in advance.
 
It could be that the table you using in the dlookup is infact a query, and in
that query you have two tables with that same field name.
 
Sorry Ofer but that is not the case. It is in fact a table.
Could someone please help with this issue?
Thanks in advance.

Ofer said:
It could be that the table you using in the dlookup is infact a query, and in
that query you have two tables with that same field name.

mrsr84 said:
I am getting the following error:
The specified field <field> could refer to more than one table listed in the
FROM clause of your SQL statement. (Error 3079)

I do not know where to findthe SQL statement to correct this. I only have
one table with the name in the dLookup coding that I am using to view the
comment text typed into a form and the course status chosen by the teacher
for a particular student in a particular course. The results are then shown
on a 1 page report. The coding I used for the dLookups are as follows:
in the field on the report called comment text:

=DLookUp([Comments],[All Students in TMS 2004 2005]!Comments," & Tables!
[All Students in TMS 2004 2005]![Comments]")

for the Course status field:

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus," &
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Please help me---I am getting totally frustrated.
Thanks in advance.
Click to expand...
Click to expand...
 
Looking again at your dlookup I noticed that you have no field to compare with

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus," &
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Is [All Students in TMS 2004 2005]!CourseStatus return a name of a table?
I dont understand your filter.

can you please send the name of the table, the name of the field you want to
filter on, the name of the field you want to get the value for, and the
variable you use to filter on
it should look like that
=dlookup("FieldnameToFind","TableName", "FieldNameToFilterOn= " & Parameter )






mrsr84 said:
Sorry Ofer but that is not the case. It is in fact a table.
Could someone please help with this issue?
Thanks in advance.

Ofer said:
It could be that the table you using in the dlookup is infact a query, and in
that query you have two tables with that same field name.

mrsr84 said:
I am getting the following error:
The specified field <field> could refer to more than one table listed in the
FROM clause of your SQL statement. (Error 3079)

I do not know where to findthe SQL statement to correct this. I only have
one table with the name in the dLookup coding that I am using to view the
comment text typed into a form and the course status chosen by the teacher
for a particular student in a particular course. The results are then shown
on a 1 page report. The coding I used for the dLookups are as follows:
in the field on the report called comment text:

=DLookUp([Comments],[All Students in TMS 2004 2005]!Comments," & Tables!
[All Students in TMS 2004 2005]![Comments]")

for the Course status field:

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus," &
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Please help me---I am getting totally frustrated.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'm not quite sure what you are trying to do with this, are you trying to
get the results for a particular student?
If so you should probably have something like:
dlookup([Comments],[All Students in TMS 2004 2005], "[StudentID] = " & a
student ID value from a form, query, report or elseware)

or what ever value for the field you are looking for. Remember the first
expression ([Comments] in your case) is the field the value will be returned
from,
the second value is the name of the table or query that the first expression
is in, and the third expression identifies the record you want, normally a
selection based on a form or report.
Good Luck
 
The table name is All Students in TMS 2004 2005 which contains 2 different
fields- 1 called comments which teachers can write text comments on a
student's progress in class and the other called Course status which is a
combo box with Passing, marginal, failing or not selected which a teacher
chooses as well. These need to show up in the report.
Hope this clarifies the needs and you are able to help me. Thanks in advance.

GH said:
I'm not quite sure what you are trying to do with this, are you trying to
get the results for a particular student?
If so you should probably have something like:
dlookup([Comments],[All Students in TMS 2004 2005], "[StudentID] = " & a
student ID value from a form, query, report or elseware)

or what ever value for the field you are looking for. Remember the first
expression ([Comments] in your case) is the field the value will be returned
from,
the second value is the name of the table or query that the first expression
is in, and the third expression identifies the record you want, normally a
selection based on a form or report.
Good Luck

mrsr84 said:
I am getting the following error:
The specified field <field> could refer to more than one table listed in
the
FROM clause of your SQL statement. (Error 3079)

I do not know where to findthe SQL statement to correct this. I only have
one table with the name in the dLookup coding that I am using to view the
comment text typed into a form and the course status chosen by the teacher
for a particular student in a particular course. The results are then
shown
on a 1 page report. The coding I used for the dLookups are as follows:
in the field on the report called comment text:

=DLookUp([Comments],[All Students in TMS 2004 2005]!Comments," & Tables!
[All Students in TMS 2004 2005]![Comments]")

for the Course status field:

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus," &
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Please help me---I am getting totally frustrated.
Thanks in advance.
Click to expand...
Click to expand...
 
How are the fields in the table linked to the student they are about. You
must have some way to sort or select by student, and that is what you use in
the third field in the dlookup.

mrsr84 said:
The table name is All Students in TMS 2004 2005 which contains 2 different
fields- 1 called comments which teachers can write text comments on a
student's progress in class and the other called Course status which is a
combo box with Passing, marginal, failing or not selected which a teacher
chooses as well. These need to show up in the report.
Hope this clarifies the needs and you are able to help me. Thanks in
advance.

GH said:
I'm not quite sure what you are trying to do with this, are you trying to
get the results for a particular student?
If so you should probably have something like:
dlookup([Comments],[All Students in TMS 2004 2005], "[StudentID] = " & a
student ID value from a form, query, report or elseware)

or what ever value for the field you are looking for. Remember the first
expression ([Comments] in your case) is the field the value will be
returned
from,
the second value is the name of the table or query that the first
expression
is in, and the third expression identifies the record you want, normally
a
selection based on a form or report.
Good Luck

mrsr84 said:
I am getting the following error:
The specified field <field> could refer to more than one table listed
in
the
FROM clause of your SQL statement. (Error 3079)

I do not know where to findthe SQL statement to correct this. I only
have
one table with the name in the dLookup coding that I am using to view
the
comment text typed into a form and the course status chosen by the
teacher
for a particular student in a particular course. The results are then
shown
on a 1 page report. The coding I used for the dLookups are as follows:
in the field on the report called comment text:

=DLookUp([Comments],[All Students in TMS 2004 2005]!Comments," &
Tables!
[All Students in TMS 2004 2005]![Comments]")

for the Course status field:

=DLookUp([CourseStatus],[All Students in TMS 2004 2005]!CourseStatus,"
&
Tables![All Students in TMS 2004 2005]![CourseStatus]")

Please help me---I am getting totally frustrated.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top