Thank you for the reply. I am only using a single query for my search form.
This is a very small db (and I'm told will remain so.. famous last words I
know) so the query is designed to pull ALL records and the form then applies
a WHERE clause based on the criteria selected. At no point does a second
query become involved. In fact I have only about 12 tables and have gone
through EACH one and there is only one table with a [Last Name] field. I have
listed the whold sql view of my query if this helps. I will go through my
tables again and make sure I didn't miss something.
Here is my qryRecordSearch, wrapped around of course to fit here. Notice the
WHERE clause showing as though I searched for the employee with the last name
EXAMPLE.
SELECT tblEmployeeData.[Last Name], tblProjectData.CITY, tblProjectData.ST,
tblProjectData.BOOKS, tblProjectData.[BUSINESS UNIT],
tblEmployeeProjectDetails.[EMP #], tblEmployeeProjectDetails.[PRJ #],
tblEmployeeProjectDetails.[Assignment Number],
tblEmployeeProjectDetails.[WK/ALLOW], tblEmployeeProjectDetails.Actuals,
tblEmployeeProjectDetails.[ST DATE], tblEmployeeProjectDetails.LENGTH,
qryProjectedEndDate.[Projected End Date],
tblEmployeeProjectDetails.DateModified,
tblEmployeeProjectDetails.TimeModified, tblEmployeeProjectDetails.MANAGER,
tblEmployeeProjectDetails.COMMENTS,
tblEmployeeProjectDetails.AssignmentLetter,
tblEmployeeProjectDetails.AssignmentStatus,
tblEmployeeProjectDetails.TaxStatus, tblEmployeeProjectDetails.HrContact,
tblEmployeeProjectDetails.HomeLeave
FROM tblProjectData INNER JOIN (tblEmployeeProjectDetails INNER JOIN
(tblEmployeeData INNER JOIN qryProjectedEndDate ON tblEmployeeData.[EMP #] =
qryProjectedEndDate.[EMP #]) ON (tblEmployeeData.[EMP #] =
tblEmployeeProjectDetails.[EMP #]) AND (tblEmployeeProjectDetails.[PRJ #] =
qryProjectedEndDate.[PRJ #]) AND (tblEmployeeProjectDetails.[EMP #] =
qryProjectedEndDate.[EMP #])) ON tblProjectData.[PRJ #] =
tblEmployeeProjectDetails.[PRJ #]
WHERE 1=1 AND [Last Name] = 'EXAMPLE'
ORDER BY tblEmployeeData.[Last Name];
Justin
hmadyson said:
Here is a guess (hard to tell without seeing the whole db). If you are using
queries in your tables, one of the queries may be producing that error. So I
would look at each of the queries that make up the overall large query and
see if you can find a place where last name comes from 2 sources.
Please let me know if I can provide more assistance.
Justin83716 said:
When I use the critieria [Last Name] in my WHERE clause
I receive the runtime error 3079
"The specified field '[Last Name]' could refer to more than one table listed
in the FROM clause of your SQL statement.
I will not post the whole SQL statement here unless it is needed, but I have
looked at it, and there is NOT a [Last Name] in more than one table. The only
table with a field [Last Name] is tblEmployeeData. So I modifed my WHERE
criteria to specifiy the specific table and specific field
(tblEmployeeData.[Last Name]) However, this does not seem to make a
difference. I still get the runtime error.
Does anyone have thoughts on why I get this error even when there appears to
be no other table with the specific field.
Thanks!
