Runtime Error 3079

L

Little Penny

I trying to create a query that will that show me all the records in
the tbl_OperatorLogJobData that that have a value greater than 2000 in
the link table field tbl_JobGrandTotals.TotalLogEnv and have a record
count of one in the link foreign key field
tbl_PeriodicCheck.OpLogJobDataID. I receive runtime error 3079:

The specified field :OpLogJobDataID" could refer to more than one
table listed in the FROM clause of your SQL statement.

My Code:

Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchByQCSign", , , , , acDialog


If IsLoaded("frmSearchByQCSign") Then


strWhere = strWhere & "([tbl_JobGrandTotals.TotalLogEnv]>2000) AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] >= " &
Format([Forms]![frmSearchByQCSign]![CboBeginningDate], conJetDate) &
") AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] <= " &
Format([Forms]![frmSearchByQCSign]![CboEndingDate], conJetDate) & ")
AND "




strSQL = "SELECT * " & _
"FROM tbl_OperatorLogJobData " & _
"LEFT JOIN tbl_JobGrandTotals ON " & _
"tbl_OperatorLogJobData.OpLogJobDataID =
tbl_JobGrandTotals.OpLogJobDataID " & _
"WHERE " & strWhere & "OpLogJobDataID in ( " & _
"SELECT OpLogJobDataID " & _
"FROM tbl_PeriodicCheck " & _
"GROUP BY OpLogJobDataID " & _
"HAVING Count(OpLogJobDataID)=1) "




Form_frm_OperatorLogJobDataViewSQL.RecordSource = strSQL
 
K

KenSheridan via AccessMonster.com

The error arises from the fact that the same column name is used by both
tables, but you have not qualified the column name with a table name, There
are a few other problems, however:

1. You are restricting the query on the TotalLogEnv column, but this is on
the right side of a LEFT OUTER JOIN. That is logically inconsistent and in
effect turns it into an INNER JOIN, so you should simply use an INNER JOIN.

2. When building the WHERE clause in code your bracketing of the table and
column names such as [tbl_OperatorLogJobData.InsertDate] is incorrect. It
would have to be [tbl_OperatorLogJobData].[InsertDate], but as (quite
correctly) no spaces or special characters are included in the table or
column names you don't need the brackets.

3. Defining the date range by on or after the start date and on or before
the end date is risky as if there happen to be values on the last day of the
range with a non-zero time of day (which Murphy's Law dictates can all too
easily creep in without you being aware of it) they won't be picked up.
Safer is to define the range by on or after the start date and before the day
following the end date.

Taking those points into account should cure things, but why build the SQL
statement in code at all rather than just referencing the controls in a query
used as the Form_frm_OperatorLogJobDataViewSQL form's RecordSource property?
All you then need to do is open the dialogue form and open the
Form_frm_OperatorLogJobDataViewSQL form from it, or if its already open just
requery it. The query would be like this:

PARAMETERS
[Forms]![frmSearchByQCSign]![CboBeginningDate] DATETIME,
[Forms]![frmSearchByQCSign]![CboEndingDate] DATETIME;
SELECT *
FROM tbl_OperatorLogJobData
INNER JOIN tbl_JobGrandTotals ON
tbl_OperatorLogJobData.OpLogJobDataID =
tbl_JobGrandTotals.OpLogJobDataID
WHERE tbl_JobGrandTotals.TotalLogEnv > 2000
AND tbl_OperatorLogJobData.InsertDate >=
[Forms]![frmSearchByQCSign]![CboBeginningDate]
AND [tbl_OperatorLogJobData.InsertDate <
DATEADD("d",1,[Forms]![frmSearchByQCSign]![CboEndingDate])
AND tbl_OperatorLogJobData.OpLogJobDataID IN
(SELECT OpLogJobDataID
FROM tbl_PeriodicCheck
GROUP BY OpLogJobDataID
HAVING COUNT(OpLogJobDataID)=1);

Note that date/time parameters should always be declared as such to avoid the
risk of the parameter values being inadvertently misinterpreted as
arithmetical expressions. It also means you don't need to worry about
formatting the parameter values as provided they are in the local system date
format or an internationally unambiguous format they'll be interpreted
correctly.

Ken Sheridan
Stafford, England

Little said:
I trying to create a query that will that show me all the records in
the tbl_OperatorLogJobData that that have a value greater than 2000 in
the link table field tbl_JobGrandTotals.TotalLogEnv and have a record
count of one in the link foreign key field
tbl_PeriodicCheck.OpLogJobDataID. I receive runtime error 3079:

The specified field :OpLogJobDataID" could refer to more than one
table listed in the FROM clause of your SQL statement.

My Code:

Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchByQCSign", , , , , acDialog

If IsLoaded("frmSearchByQCSign") Then

strWhere = strWhere & "([tbl_JobGrandTotals.TotalLogEnv]>2000) AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] >= " &
Format([Forms]![frmSearchByQCSign]![CboBeginningDate], conJetDate) &
") AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] <= " &
Format([Forms]![frmSearchByQCSign]![CboEndingDate], conJetDate) & ")
AND "


strSQL = "SELECT * " & _
"FROM tbl_OperatorLogJobData " & _
"LEFT JOIN tbl_JobGrandTotals ON " & _
"tbl_OperatorLogJobData.OpLogJobDataID =
tbl_JobGrandTotals.OpLogJobDataID " & _
"WHERE " & strWhere & "OpLogJobDataID in ( " & _
"SELECT OpLogJobDataID " & _
"FROM tbl_PeriodicCheck " & _
"GROUP BY OpLogJobDataID " & _
"HAVING Count(OpLogJobDataID)=1) "




Form_frm_OperatorLogJobDataViewSQL.RecordSource = strSQL
Click to expand...
 
L

Little Penny

Ken thank you very much.


Thank you
Thank you
Thank you





The error arises from the fact that the same column name is used by both
tables, but you have not qualified the column name with a table name, There
are a few other problems, however:

1. You are restricting the query on the TotalLogEnv column, but this is on
the right side of a LEFT OUTER JOIN. That is logically inconsistent and in
effect turns it into an INNER JOIN, so you should simply use an INNER JOIN.

2. When building the WHERE clause in code your bracketing of the table and
column names such as [tbl_OperatorLogJobData.InsertDate] is incorrect. It
would have to be [tbl_OperatorLogJobData].[InsertDate], but as (quite
correctly) no spaces or special characters are included in the table or
column names you don't need the brackets.

3. Defining the date range by on or after the start date and on or before
the end date is risky as if there happen to be values on the last day of the
range with a non-zero time of day (which Murphy's Law dictates can all too
easily creep in without you being aware of it) they won't be picked up.
Safer is to define the range by on or after the start date and before the day
following the end date.

Taking those points into account should cure things, but why build the SQL
statement in code at all rather than just referencing the controls in a query
used as the Form_frm_OperatorLogJobDataViewSQL form's RecordSource property?
All you then need to do is open the dialogue form and open the
Form_frm_OperatorLogJobDataViewSQL form from it, or if its already open just
requery it. The query would be like this:

PARAMETERS
[Forms]![frmSearchByQCSign]![CboBeginningDate] DATETIME,
[Forms]![frmSearchByQCSign]![CboEndingDate] DATETIME;
SELECT *
FROM tbl_OperatorLogJobData
INNER JOIN tbl_JobGrandTotals ON
tbl_OperatorLogJobData.OpLogJobDataID =
tbl_JobGrandTotals.OpLogJobDataID
WHERE tbl_JobGrandTotals.TotalLogEnv > 2000
AND tbl_OperatorLogJobData.InsertDate >=
[Forms]![frmSearchByQCSign]![CboBeginningDate]
AND [tbl_OperatorLogJobData.InsertDate <
DATEADD("d",1,[Forms]![frmSearchByQCSign]![CboEndingDate])
AND tbl_OperatorLogJobData.OpLogJobDataID IN
(SELECT OpLogJobDataID
FROM tbl_PeriodicCheck
GROUP BY OpLogJobDataID
HAVING COUNT(OpLogJobDataID)=1);

Note that date/time parameters should always be declared as such to avoid the
risk of the parameter values being inadvertently misinterpreted as
arithmetical expressions. It also means you don't need to worry about
formatting the parameter values as provided they are in the local system date
format or an internationally unambiguous format they'll be interpreted
correctly.

Ken Sheridan
Stafford, England

Little said:
I trying to create a query that will that show me all the records in
the tbl_OperatorLogJobData that that have a value greater than 2000 in
the link table field tbl_JobGrandTotals.TotalLogEnv and have a record
count of one in the link foreign key field
tbl_PeriodicCheck.OpLogJobDataID. I receive runtime error 3079:

The specified field :OpLogJobDataID" could refer to more than one
table listed in the FROM clause of your SQL statement.

My Code:

Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchByQCSign", , , , , acDialog

If IsLoaded("frmSearchByQCSign") Then

strWhere = strWhere & "([tbl_JobGrandTotals.TotalLogEnv]>2000) AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] >= " &
Format([Forms]![frmSearchByQCSign]![CboBeginningDate], conJetDate) &
") AND "
strWhere = strWhere & "([tbl_OperatorLogJobData.InsertDate] <= " &
Format([Forms]![frmSearchByQCSign]![CboEndingDate], conJetDate) & ")
AND "


strSQL = "SELECT * " & _
"FROM tbl_OperatorLogJobData " & _
"LEFT JOIN tbl_JobGrandTotals ON " & _
"tbl_OperatorLogJobData.OpLogJobDataID =
tbl_JobGrandTotals.OpLogJobDataID " & _
"WHERE " & strWhere & "OpLogJobDataID in ( " & _
"SELECT OpLogJobDataID " & _
"FROM tbl_PeriodicCheck " & _
"GROUP BY OpLogJobDataID " & _
"HAVING Count(OpLogJobDataID)=1) "




Form_frm_OperatorLogJobDataViewSQL.RecordSource = strSQL
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

Query Records in Linked table 4
Filter Text Syntax 2
Search working in one form but not another 4
Listbox coding problem 8
error 3464 data type mismatch in criteria expression 0
Run Time Errors 3
Runtime Error '2448': 4
'Runtime Error 2448' 2

Top