Check out some similar questions!

mcleyn · Jun 12, 2009, 06:14 AM

Hi,

I am using Access 2000.
I have a form that shows all the projects being held inside my database.
I have made drop down boxes in the form with VBA to filter the projects.
I am getting an error when I try to filter the projects:

Run Time Error '3079':
The specified field 'FiscalYear' could refer to more than one table listed in the FROM clause of your SQL statement.

What does this mean?

Perito · Jun 12, 2009, 07:03 AM

The message indicates that the compiler can't figure out which table you're trying to access. Is "FiscalYear" a column title in more than one table? As an experiment, you could change one of the "FiscalYear"s to "Fiscal_Year". If that works, then change it back and try to specify the table name more explicitly in the FROM clause.

To be more specific, you're probably trying to say "SELECT ... FROM ... WHERE" and whatever you're trying to select is not unique.

The syntax for fully qualified tables names is [server].[databasename].[owner].[table name].

mcleyn · Jun 12, 2009, 07:09 AM

Originally Posted by Perito

The message indicates that the compiler can't figure out which table you're trying to access. Is "FiscalYear" a column title in more than one table? As an experiment, you could change one of the "FiscalYear"s to "Fiscal_Year". If that works, then change it back and try to specify the table name more explicitly in the FROM clause.

To be more specific, you're probably trying to say "SELECT ... FROM ... WHERE" and whatever you're trying to select is not unique.

The syntax for fully qualified tables names is [server].[databasename].[owner].[table name].

Thank you very much. I just changed the name to Fiscal_Year and I no longer get the error.

ScottGem · Jun 12, 2009, 12:19 PM

Perito is right about the error, but I think your solution may not the best choice. Rather than changing the name of a field, you would be better to fully qualify your field names.

In Access a fully qualified names is just [tablename].[fieldname]. So somewhere in the SQL statement was the fieldname FiscalYear. All you need to do is indicate what table that instance of FiscalYear was looking at by prefacing it with the tablename. If you want to post your SQL statement we can suggest where to change it.

mcleyn · Jun 15, 2009, 11:01 AM

Originally Posted by ScottGem

Perito is right about the error, but I think your solution may not the best choice. Rather than changing the name of a field, you would be better to fully qualify your field names.

In Access a fully qualified names is just [tablename].[fieldname]. So somewhere in the SQL statement was the fieldname FiscalYear. All you need to do is indicate what table that instance of FiscalYear was looking at by prefacing it with the tablename. If you want to post your SQL statement we can suggest where to change it.

I understand what your are saying but am still a little unsure of how to correct it in my sql statement. Again the error specifies it is in the FROM clause of my sql statement, however what fiscal year do I change.
Here is my sql statement:

'only Province, ProjectType and Status selected
If (budgetItemsTemp = " ") And (fiscalYearTemp = " ") And (categoryTemp = " ") Then
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType " _
& "FROM tblMain "

'only cboBudgetItems and previous selected
ElseIf (fiscalYearTemp = " ") And (categoryTemp = " ") Then
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblBudgetItems.BudgetItems " _
& "FROM tblMain LEFT JOIN tblBudgetItems ON tblMain.ProjectCode = tblBudgetItems.ProjectCode "

'only cboCategory and original three selected
ElseIf (budgetItemsTemp = " ") And (fiscalYearTemp = " ") Then
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblCategory.PolicyArea, tblCategory.Priority, tblCategory.ScheduleAExceptions " _
& "FROM tblMain LEFT JOIN tblCategory ON tblMain.ProjectCode = tblCategory.ProjectCode "

'only cboFiscalYear and original three selected
ElseIf (budgetItemsTemp = " ") And (categoryTemp = " ") Then
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblFiscalBudget.FiscalYear, tblFiscalBudget.Budget " _
& "FROM tblMain LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode "

ElseIf (fiscalYearTemp = " ") Then 'all except cboFiscalYear selected
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblBudgetItems.BudgetItems, tblCategory.PolicyArea, tblCategory.Priority, " _
& "tblCategory.ScheduleAExceptions " _
& "FROM (tblMain LEFT JOIN tblBudgetItems ON tblMain.ProjectCode = tblBudgetItems.ProjectCode) " _
& "LEFT JOIN tblCategory ON tblBudgetItems.ProjectCode = tblCategory.ProjectCode "

ElseIf (budgetItemsTemp = " ") Then 'all except cboBudgetItems selected
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblFiscalBudget.FiscalYear, tblFiscalBudget.Budget, tblCategory.PolicyArea, " _
& "tblCategory.Priority, tblCategory.ScheduleAExceptions " _
& "FROM (tblMain LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode) " _
& "LEFT JOIN tblCategory ON tblFiscalBudget.ProjectCode = tblCategory.ProjectCode "

ElseIf (categoryTemp = " ") Then 'all except cboCategory selected
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblFiscalBudget.FiscalYear, tblFiscalBudget.Budget, tblBudgetItems.BudgetItems " _
& "FROM (tblMain LEFT JOIN tblBudgetItems ON tblMain.ProjectCode = tblBudgetItems.ProjectCode) " _
& "LEFT JOIN tblFiscalYear ON tblBudgetItems.ProjectCode = tblFiscalYear.ProjectCode "

Else 'everything selected
sql = "SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, " _
& "tblMain.ProjectType, tblFiscalBudget.FiscalYear, tblFiscalBudget.Budget, tblBudgetItems.BudgetItems, " _
& "tblCategory.PolicyArea, tblCategory.Priority, tblCategory.ScheduleAExceptions " _
& "FROM ((tblMain LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode) " _
& "LEFT JOIN tblBudgetItems ON tblFiscalBudget.ProjectCode = tblBudgetItems.ProjectCode) " _
& "LEFT JOIN tblCategory ON tblBudgetItems.ProjectCode = tblCategory.ProjectCode "
End If

ScottGem · Jun 15, 2009, 01:41 PM

I can't see anywhere its not fully qualified. So you need to use Debug tools to step through the code and see WHICH SQL statement is causing the error.

mcleyn · Jun 16, 2009, 06:13 AM

Originally Posted by ScottGem

I can't see anywhere its not fully qualified. So you need to use Debug tools to step through the code and see WHICH SQL statement is causing the error.

Exactly I thought so to. So I debugged the error and it gives me a highlighted: 'Me.RecordSource = sql'
This highlighted area is below the sql that I just posted. Then I assumed the error was somewhere in my sql but that is what I just posted and it doesn't look like anything is wrong.

ScottGem · Jun 16, 2009, 06:22 AM

OK, You really need to learn how to use the Debug tools. In the VBE, there is a Locals window. In that window, its will show the current value of the sql variable. If you can't read the whole thing thing, then add a line:

Debug.Print sql

before the highlighted line and it will display the value of sql in the Immediate window.

mcleyn · Jun 16, 2009, 07:23 AM

Originally Posted by ScottGem

OK, You really need to learn how to use the Debug tools. In the VBE, there is a Locals window. In that window, its will show the current value of the sql variable. If you can't read the whole thing thing, then add a line:

Debug.Print sql

before the highlighted line and it will display the value of sql in the Immediate window.

Ok so I have Debug.Print sql and this is what I was given:

SELECT tblMain.ProjectCode, tblMain.EventDate, tblMain.Status, tblMain.OrganizationName, tblMain.Province, tblMain.ProjectType, tblFiscalBudget.FiscalYear, tblFiscalBudget.Budget, tblBudgetItems.BudgetItems, tblCategory.PolicyArea, tblCategory.Priority, tblCategory.ScheduleAExceptions
FROM ((tblMain LEFT JOIN tblFiscalBudget ON tblMain.ProjectCode = tblFiscalBudget.ProjectCode) LEFT JOIN tblBudgetItems ON tblFiscalBudget.ProjectCode = tblBudgetItems.ProjectCode) LEFT JOIN tblCategory ON tblBudgetItems.ProjectCode = tblCategory.ProjectCode WHERE (FiscalYear = '11/12') ORDER BY tblMain.ProjectCode;

ScottGem · Jun 16, 2009, 08:06 AM

Bingo! There it is. The code you originally posted didn't show any WHERE clauses. There must be addditional code that added the WHERE clause. So, do you see it now?

mcleyn · Jun 16, 2009, 11:14 AM

Originally Posted by ScottGem

Bingo! There it is. The code you originally posted didn't show any WHERE clauses. There must be addditional code that added the WHERE clause. So, do you see it now?

Ha! I see!
So I shoud add this:

WHERE (FiscalYear = '11/12') ORDER BY tblMain.ProjectCode;

to the original code that I posted?

ScottGem · Jun 16, 2009, 11:30 AM

Somewhere in your code its adding a WHERE clause to the sql variable. You need to find where that's happening. Then you need to fully qualify the FiscalYear field by indicating the table its being drawn from.

What I don't know is where that will be.

Looking through the code you previously posted, it appears you are always pulling FiscalYear from tblFiscalBudget. So would change the WHERE clause to:
WHERE (tblFiscalBudget.FiscalYear =

I would suspect, you are concatening in a value on the other side of the equals sign.

mcleyn · Jun 16, 2009, 12:44 PM

Originally Posted by ScottGem

Somewhere in your code its adding a WHERE clause to the sql variable. You need to find where that's happening. Then you need to fully qualify the FiscalYear field by indicating the table its being drawn from.

What I don't know is where that will be.

Looking through the code you previously posted, it appears you are always pulling FiscalYear from tblFiscalBudget. So would change the WHERE clause to:
WHERE (tblFiscalBudget.FiscalYear =

I would suspect, you are concatening in a value on the other side of the equals sign.

This is the only WHERE clause in all my code:

sql = sql & "WHERE (" & Me.Filter & ") ORDER BY tblMain.ProjectCode;"

So should I add the WHERE (tblFiscalBudget.FiscalYear.. to this?

Yes I am concatening a value on the other side '11/12' stands for the years 2011 and 2012.

ScottGem · Jun 16, 2009, 12:57 PM

Ahh I see. This presents a problem. I thionk what you are going to need to do is test the left side of Me.Filter and add the table qualifier accordingly.

For example:

strField = Left(Me.Filter,Instr(1,Me.Filter,"=")-2)

If strField = "[FiscalYear]" Then
sql = sql & "WHERE (tblFiscalBudgetYear]." & Me.Filter & ") ORDER BY tblMain.ProjectCode;"
End If

You ma need other possibilites if you can use different filters.