Error 3079 (1 Viewer)

L

Ladyoftheputers

New member
Local time
Today, 04:48
Joined
Sep 29, 2011
Messages
6
:confused: Hello! New to the forum, but not programming nor Access. I'm still kinda "old" skewel in Access & programming, but trying to catch up! :rolleyes: I hesitated to post as this will be rather long but, since I'm banging my head!!!:mad: 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! :D 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. :confused:
Thanks in advance for any help or ideas! :p

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.
 
Last edited:
V

vbaInet

AWF VIP
Local time
Today, 09:48
Joined
Jan 22, 2010
Messages
26,374
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:
Code: 
 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

MSAccessRookie

AWF VIP
Local time
Today, 04:48
Joined
May 2, 2008
Messages
3,428
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.
 
L

Ladyoftheputers

New member
Local time
Today, 04:48
Joined
Sep 29, 2011
Messages
6
vbaInet:
I changed the line of code to this:
Code: 
 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! :rolleyes:
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:
Code: 
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:
Code: 
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!! :D
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..:confused:
Again: Thanks all!!:p
 
V

vbaInet

AWF VIP
Local time
Today, 09:48
Joined
Jan 22, 2010
Messages
26,374
Upload a stripped down version of your db and we'll take a look. Obviously mention the trouble form ;)
 
L

Ladyoftheputers

New member
Local time
Today, 04:48
Joined
Sep 29, 2011
Messages
6
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....:eek:
 
Last edited:
L

Ladyoftheputers

New member
Local time
Today, 04:48
Joined
Sep 29, 2011
Messages
6
Hello again all! Hi Bob! Nice to meet you!

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

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.:p

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

Thanks All!:cool:
 

Attachments

  • Fix EOM Reports.zip
    392 KB · Views: 116
V

vbaInet

AWF VIP
Local time
Today, 09:48
Joined
Jan 22, 2010
Messages
26,374
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.
 

Attachments

  • Fix EOM Reports.zip
    396.7 KB · Views: 134
L

Ladyoftheputers

New member
Local time
Today, 04:48
Joined
Sep 29, 2011
Messages
6
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! :)
 
V

vbaInet

AWF VIP
Local time
Today, 09:48
Joined
Jan 22, 2010
Messages
26,374
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.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom