Problem with complex sql statement underlying a form

[Jack]

Hi,
I have a sql statement underlying a form. This sql statement combines two
BizUnit fields of two tables into one and this works fine:

SELECT tblActionRequest.*, nz([tblOpNonOp].[BizUnit],[tblPlant].[BizUnit])
AS BizUnit, Left([RequestDate],InStr([RequestDate]," ")) AS RequestDate1
FROM (tblActionRequest LEFT JOIN tblOpNonOp ON tblActionRequest.OpNonOp =
tblOpNonOp.ID) LEFT JOIN tblPlant ON tblActionRequest.Plant = tblPlant.Plant

However, I am using a serach form to get records displayed in this form
based on a search criteri. This seach criteria forms the where caluse.Thus
with the where clause I am getting the following sql statement unerlying the
form:
SELECT tblActionRequest.*, nz([tblOpNonOp].[BizUnit],[tblPlant].[BizUnit])
AS BizUnit, Left([RequestDate],InStr([RequestDate]," ")) AS RequestDate1
FROM (tblActionRequest LEFT JOIN tblOpNonOp ON tblActionRequest.OpNonOp =
tblOpNonOp.ID) LEFT JOIN tblPlant ON tblActionRequest.Plant = tblPlant.Plant
where [BizUnit]='Thermosafe' And [Originator]='Bean Gan' And [RequestDate]
Between #5/12/2008# And #5/14/2008#

However this sql statement does not run and throws error as follows:
The specified field [BizUnit] could refer to more than one table listed in
the From clause of your sql statement
I would appreciate any help for reslution of this problem. Thanks.

 

[Jack]

Thanks PJFry and Dirk for the help. I appreciate it. I am going to try those
out.

Hi,
I have a sql statement underlying a form. This sql statement combines two
BizUnit fields of two tables into one and this works fine:

SELECT tblActionRequest.*, nz([tblOpNonOp].[BizUnit],[tblPlant].[BizUnit])
AS BizUnit, Left([RequestDate],InStr([RequestDate]," ")) AS RequestDate1
FROM (tblActionRequest LEFT JOIN tblOpNonOp ON tblActionRequest.OpNonOp =
tblOpNonOp.ID) LEFT JOIN tblPlant ON tblActionRequest.Plant =
tblPlant.Plant

However, I am using a serach form to get records displayed in this form
based on a search criteri. This seach criteria forms the where caluse.Thus
with the where clause I am getting the following sql statement unerlying
the
form:
SELECT tblActionRequest.*, nz([tblOpNonOp].[BizUnit],[tblPlant].[BizUnit])
AS BizUnit, Left([RequestDate],InStr([RequestDate]," ")) AS RequestDate1
FROM (tblActionRequest LEFT JOIN tblOpNonOp ON tblActionRequest.OpNonOp =
tblOpNonOp.ID) LEFT JOIN tblPlant ON tblActionRequest.Plant =
tblPlant.Plant
where [BizUnit]='Thermosafe' And [Originator]='Bean Gan' And [RequestDate]
Between #5/12/2008# And #5/14/2008#

However this sql statement does not run and throws error as follows:
The specified field [BizUnit] could refer to more than one table listed in
the From clause of your sql statement
I would appreciate any help for reslution of this problem. Thanks.

I believe you'll have to replace the reference to [BizUnit] in the WHERE
clause with the expression that you used to merge the two different BizUnit
fields. So instead of this:

where [BizUnit]='Thermosafe'

.... you should have this:

where nz([tblOpNonOp].[BizUnit],[tblPlant].[BizUnit])='Thermosafe'

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)