Error 3079

[Ladyoftheputers]

Hello! New to the forum, but not programming nor Access. I'm still kinda "old" skewel in Access & programming, but trying to catch up! I hesitated to post as this will be rather long but, since I'm banging my head!!! Anyway, my prob... I've 'migrated' a db from A97 to 2007. After several months, I've FINALLY got it compressed, compiled & working (mostly) in 2007! I said mostly because there are a few probs.. the main one which is really bugging me is this 3079 error... it says that my specified field could refer to more than one table listed in the FROM clause of my SQL. Okay, I could understand that under normal circumstances, but the thing is....this field is only in two tables in the entire db...and only one table that has that field is being referenced anywhere in any of the SQL statements. The error is coming when I have a popup that a user selects which date range they want to use to print the report. The VBA line is:
DoCmd.OpenReport "MBookings", acViewPreview, "MBookingsQuery", "(([Status.StatusCode] = 'S') AND (([MBookingsQuery]![Jobs.ResolutionsDate]) >= [Forms]![frmReportDateRanges]![txtBeginDate]) AND (([MBookingsQuery]![Jobs.ResolutionDate]) <= [Forms]![frmReportDateRanges]![txtEndDate]))"
The field is "Jobs.ResolutionsDate". The criteria query is:
SELECT DISTINCTROW Status.StatusCode, Status.StatusName, Employees.LastName, Employees.FirstName, Jobs.ResolutionDate, PContracts.ContractNumber, Jobs.JobID, Jobs.LocationID, Jobs.JobName, Jobs.BaseBid AS OldBaseBid, IIf((IsNull([CalcTotal])),[OldBaseBid],[CalcTotal]) AS BaseBid, [RegUndecMat]+[RegUndecTax]+[RegUndecLabor]+[RegUndecTON]+[RegUndecSubcontracts]+[RegUndecGM] AS CalcTotal, Jobs.GrossMargin, Jobs.ManDays, Locations.LocationName, IIf([BaseBid]=0,"0",[GrossMargin]/[BaseBid]) AS [GM%], IIf((IsError([GrossMargin]/[ManDays])),0,[GrossMargin]/[ManDays]) AS [GM/MD], PContracts.CustomerID, Customers.CustomerName, Customers.CustCode, Employees.EmployeeCode
FROM Customers RIGHT JOIN (((Status INNER JOIN (Locations INNER JOIN (Employees INNER JOIN Jobs ON Employees.EmployeeID = Jobs.EmployeeID) ON Locations.LocationID = Jobs.LocationID) ON Status.StatusID = Jobs.StatusID) INNER JOIN PContracts ON Jobs.JobID = PContracts.JobID) LEFT JOIN qryRegUndecTotals ON PContracts.ContractNumber = qryRegUndecTotals.ContractNumber) ON Customers.CustomerID = PContracts.CustomerID
ORDER BY Employees.LastName, Jobs.ResolutionDate, PContracts.ContractNumber, Jobs.JobID;
Yes, it has another query, which is:
SELECT DISTINCTROW qryInvoiceTotals.ContractNumber, IIf((IsNull([qryUndecidedTotals]![SumOfMaterial])),[qryInvoiceTotals]![SumOfMaterial],([qryInvoiceTotals]![SumOfMaterial]+[qryUndecidedTotals]![SumOfMaterial])) AS RegUndecMat, IIf((IsNull([qryUndecidedTotals]![SumOfTax])),[qryInvoiceTotals]![SumOfTax],([qryInvoiceTotals]![SumOfTax]+[qryUndecidedTotals]![SumOfTax])) AS RegUndecTax, IIf((IsNull([qryUndecidedTotals]![SumOfLabor])),[qryInvoiceTotals]![SumOfLabor],([qryInvoiceTotals]![SumOfLabor]+[qryUndecidedTotals]![SumOfLabor])) AS RegUndecLabor, IIf((IsNull([qryUndecidedTotals]![SumOfTrips&O/N])),[qryInvoiceTotals]![SumOfTrips&O/N],([qryInvoiceTotals]![SumOfTrips&O/N]+[qryUndecidedTotals]![SumOfTrips&O/N])) AS RegUndecTON, IIf((IsNull([qryUndecidedTotals]![SumOfSubcon])),[qryInvoiceTotals]![SumOfSubcontracts],([qryInvoiceTotals]![SumOfSubcontracts]+[qryUndecidedTotals]![SumOfSubcon])) AS RegUndecSubcontracts, IIf((IsNull([qryUndecidedTotals]![SumOfGM])),[qryInvoiceTotals]![SumOfGM],([qryInvoiceTotals]![SumOfGM]+[qryUndecidedTotals]![SumOfGM])) AS RegUndecGM, IIf((IsNull([qryUndecidedTotals]![SumOfMDFab])),[qryInvoiceTotals]![SumOfFabMDs],([qryInvoiceTotals]![SumOfFabMDs]+[qryUndecidedTotals]![SumOfMDFab])) AS RegUndecFabMD, IIf((IsNull([qryUndecidedTotals]![SumOfMDInstall])),[qryInvoiceTotals]![SumOfInstallMDs],([qryInvoiceTotals]![SumOfInstallMDs]+[qryUndecidedTotals]![SumOfMDInstall])) AS RegUndecInstMD
FROM (PContracts LEFT JOIN qryInvoiceTotals ON PContracts.ContractNumber=qryInvoiceTotals.ContractNumber) LEFT JOIN qryUndecidedTotals ON PContracts.ContractNumber=qryUndecidedTotals.ContractNumber
ORDER BY qryInvoiceTotals.ContractNumber;
Okay, by now you should trust me when I say that there are only 2 tables with this field name and only 1 is being referenced.
If there is anyone still with me...any ideas? Oh, in the 97 version, this works.
Thanks in advance for any help or ideas!

BTW: The queries all run. Another place this report is printed with date ranges input by user and it runs perfectly. Alas, it is not being called thru VB but by a macro. It's called by VB here(I think - this isn't my code, I'm just band-aiding!) because it is just the first of several reports being called and printed at once using the same date range.

 

[vbaInet]

Welcome to the forum!

Since you're new, I should point you to this nice little thread that shows you how to put your code in Code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

You can appreciate that it's quite difficult reading your sql statements as it currently is

So, looking at your OpenReport command, should it not be:

 DoCmd.OpenReport "MBookings", acViewPreview, "MBookingsQuery", "[COLOR=Red][Status].[/COLOR][StatusCode] = 'S' AND [COLOR=Red][Jobs].[/COLOR][ResolutionsDate] >= [Forms]![frmReportDateRanges]![txtBeginDate] AND [COLOR=Red][Jobs].[/COLOR][ResolutionDate] <= [Forms]![frmReportDateRanges]![txtEndDate]"

By the way, I removed the parens because you're using the AND operator so no grouping is required.

 

[MSAccessRookie]

There appear to be two additional Queries that are not displayed here. If you look at qryUndecidedTotals and qryInvoiceTotals you might find additional information to consider. If nothing jumps out at you, displaying them for us will allow us to further assist you.

 

[Ladyoftheputers]

vbaInet:
I changed the line of code to this:

 DoCmd.OpenReport "MBookings", acPreview, "MBookingsQuery", "[Status].[StatusCode]='S' AND [MBookingsQuery]![Jobs].[ResolutionDate] >= [Forms]![frmReportDateRanges]![txtBeginDate] AND [MBookingsQuery]![Jobs.ResolutionDate] <= [Forms]![frmReportDateRanges]![txtEndDate]"

BTW, I had put my earlier post in the code brackets, but must have done something to it!
I still get the exact same error, on the exact same field.
MSAccessRookie:
I've looked at both and nothing jumps out so I'll post...
qryUndecidedTotals:

SELECT DISTINCTROW Undecided.ContractNumber, Sum(Undecided.Material) AS SumOfMaterial, Sum(Undecided.Tax) AS SumOfTax, Sum(Undecided.Labor) AS SumOfLabor, Sum(Undecided.MDFab) AS SumOfMDFab, Sum(Undecided.MDInstall) AS SumOfMDInstall, Sum(Undecided.[Trips&O/N]) AS [SumOfTrips&O/N], Sum(Undecided.Subcon) AS SumOfSubcon, Sum(Undecided.GM) AS SumOfGM, Sum([Material]+[Tax]+[Labor]+([Trips&O/N])+[Subcon]+[GM]) AS UPhaseTotal, Undecided.PercentComplete, Sum((([Material]+[Tax]+[Labor]+[Trips&O/N]+[Subcon]+[GM])*[PercentComplete])/100) AS ULineValCom
FROM ((PContracts INNER JOIN Jobs ON PContracts.JobID=Jobs.JobID) INNER JOIN (Phases INNER JOIN Undecided ON Phases.PhaseID=Undecided.PhaseID) ON PContracts.ContractNumber=Undecided.ContractNumber) INNER JOIN Locations ON Jobs.LocationID=Locations.LocationID
GROUP BY Undecided.ContractNumber, Undecided.PercentComplete;

qryInvoiceTotals:

SELECT DISTINCTROW InvoicePhases.ContractNumber, Sum(InvoicePhases.Material) AS SumOfMaterial, Sum(InvoicePhases.Tax) AS SumOfTax, Sum(InvoicePhases.Labor) AS SumOfLabor, Sum(InvoicePhases.[Trips&O/N]) AS [SumOfTrips&O/N], Sum(InvoicePhases.Subcontracts) AS SumOfSubcontracts, Sum(InvoicePhases.GM) AS SumOfGM, Sum(InvoicePhases.FabMDs) AS SumOfFabMDs, Sum(InvoicePhases.InstallMDs) AS SumOfInstallMDs
FROM InvoicePhases
GROUP BY InvoicePhases.ContractNumber;

I truly really appreciate ALL help!!
This is an inherited db, last I used Access was 2000...lol
As I stated earlier, I COULD understand this, if there was more than 1 table being called that had this field or similar in it...but there isn't..
Again: Thanks all!!

 

[vbaInet]

Upload a stripped down version of your db and we'll take a look. Obviously mention the trouble form

 

[Ladyoftheputers]

vbaInet:
I have it stripped as low as I can and still reproduce the error...it's still at 4 MG...
I'll upload it if you want...


Ok, I have it down to a little over 2 MG....

 

[boblarson]

...it's still at 4 MG...

Even if you run COMPACT AND REPAIR and then zip it by right-clicking on the file and selecting SEND TO > COMPRESSED FOLDER?

 

[Ladyoftheputers]

Hello again all! Hi Bob! Nice to meet you!

Ok, I've tried this once already and it didn't work but perseverance!!

I've got the stripped down version to a zip and uploading it with this.

The stripped down version started as a blank db. I imported the tables, queries, reports, macro's and General & Math modules. I re-created the offending form and VB from scratch. Every single query that is in this works! I checked.

I greatly appreciate everyone's help on this, it's really starting to frustrate me!

Thanks All!

 

[vbaInet]

See attached and note what was changed. Some things that were done didn't make sense.

For example:
1. The filter applied to the report (via the command) is the same as the Record Source of the report.
2. The fields being referenced in the WHERE part of the command is using a full reference to the tables in the query, whereas the report is based on the query and hence, just the field name should suffice.

 

[Ladyoftheputers]

vbaInet,
I apologize for taking so long to reply. I have been swamped @ work with other things and have not had a chance to check out your generous contribution! I gratefully thank you and as soon as I have gone through this, I will post back. Thank you so much!

 

[vbaInet]

I don't even remember what the entire thread is all about because it's been a week

But let us know how things go.