[AccessD] Weird query question

Mon Jan 3 11:55:17 CST 2005

Do you mean the query is based on another (sub) query?  Can you combine
them?  I was thinking your subquery was part of the original query's SQL.

Drew

-----Original Message-----
From: Bobby Heid [mailto:bheid at appdevgrp.com]
Sent: Monday, January 03, 2005 11:03 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Weird query question

Drew,

But the how do I pass parameters to the sub query.

Basically, the subquery is summing payroll records for a given period for a
contract.  Therefore, the subquery has to run before the data can be added
to the main query.

If I understand your answer, adding the parameters in the where clause could
cause contracts to not show up if they did not have payroll in the selected
period.

I have a feeling that I might need to create a work table, dump the
contracts to it, then update the payroll with a second query.

Thanks,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Monday, January 03, 2005 11:32 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Weird query question

Same way....

"SELECT ........ And Format(" & Forms.("PerformanceChooser").RptTo  &
",""yyyymm""))......"

Actually, you could drop the Format from the SQL, and just have your code
format it inside the string.

Drew

-----Original Message-----
From: Bobby Heid [mailto:bheid at appdevgrp.com]
Sent: Monday, January 03, 2005 10:26 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Weird query question

Yes, I understand what you are talking about.  But, only the main query
accesses the tempvars form, the subquery (which is what was changed) now
accesses another form.  

How would I give the parameters in the subquery like you suggested?

Thanks,
Bobby

Here's the subquery:

SELECT [Payroll Line Items].[Contract ID], Sum(nz([Payroll])) AS
ActualPayroll FROM [Payroll Line Items] WHERE ((([Payroll Line
Items].CostType)="WC") 
	AND ((Format([Start Date],"yyyymm")) 
		Between
Format([forms]![PerformanceChooser]![RptFrom],"yyyymm") 
		And Format([forms]![PerformanceChooser]![RptTo],"yyyymm")) 
	AND ((Format([DateAdded],"yyyymmdd")) 
		Between
Format([forms]![PerformanceChooser]![txtEntryFrom],"yyyymmdd") 
		And
Format([forms]![PerformanceChooser]![txtEntryTo],"yyyymmdd")))
GROUP BY [Payroll Line Items].[Contract ID];

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Monday, January 03, 2005 10:59 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Weird query question

As gustav already pointed out, the problem lies with the several references
to [Forms]![TempVars].  That works in a saved query, but if you try to put
that into the SQL of a recordset, it does not go looking to the form
TempVars for answers, it just considers it an unknown expression.  So,
instead, when putting it into SQL, you can just put the values like so:

"SELECT .............(lots of SQL)....IIf(Len(" &
Forms("TempVars").ContractorID & ")>0,.....more SQL...."

Make sense?

Drew

-----Original Message-----
From: Bobby Heid [mailto:bheid at appdevgrp.com]
Sent: Monday, January 03, 2005 9:32 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Weird query question

ContractActualPayrollByDates is the subquery that was changed from
ContractActualPayroll.  I just took this SQL from the designer (where it
does work).

Thanks,
Bobby

Here it is:

SELECT Project.Project, 
	Contractor.[Company Name], 
		[Curr Contract Value]-([Curr WC Deduct]+[Curr GL Deduct]+
		[CurrSubInsDeduct]+[Curr Umb
Deduct]+[CurMisc1]+[CurMisc2]+[CurMisc3]) AS Volume, 
	IIf(nz([Curr Estimated Payroll],0)>=nz([ActualPayroll],0),[Curr
Estimated Payroll],
		[ActualPayroll]) AS EstLabor, 
	IIf([Volume]<>0,Format(([EstLabor]/[Volume]),'#.000%'),'N/A') AS
estLaborper, 
	Contract.[Curr GL Deduct] AS EstGLDed, 
	IIf([Volume]<>0,Format(([Curr GL Deduct]/[volume]),'#.000%'),'N/A')
AS EstGLDedPer,
	Contract.[Curr WC Deduct] AS EstWCDed, 
	IIf([Volume]<>0,Format(([Curr WC Deduct]/[volume]),'#.000%'),'N/A')
AS EstWCDedPct,
	[EstGLDed]+[EstWCDed] AS EstTotal, 
	IIf([Volume]<>0,Format(([EstTotal]/[volume]),'#.000%'),'N/A') AS
EstTotalPer, 
	IIf(nz([Curr Estimated Payroll],0)>=nz([ActualPayroll],0),'*','') AS
EstLaborInd,
 	ContractActualPayrollByDates.ActualPayroll
FROM ((Contract 
	LEFT JOIN ContractActualPayrollByDates 
		ON Contract.[Contract ID] =
ContractActualPayrollByDates.[Contract ID]) 
	LEFT JOIN Contractor ON Contract.[Contractor ID] =
Contractor.[Contractor ID]) 
	LEFT JOIN Project ON Contract.ProjectID = Project.[Project ID] WHERE
(((Contract.[Contractor ID])=
	IIf(Len([Forms]![TempVars].[ContractorID])>0,
		[Forms]![TempVars].[ContractorID],[Contract].[Contractor
ID])) 
	AND ((Contract.ProjectID)=
		IIf(Len([Forms]![TempVars].[txtProjectID])>0,

[Forms]![TempVars].[txtProjectID],[Contract].[ProjectID])))
ORDER BY Project.Project ASC, Contractor.[Company Name] ASC;

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Monday, January 03, 2005 10:02 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Weird query question

Can you post the SQL?

Drew

-----Original Message-----
From: Bobby Heid [mailto:bheid at appdevgrp.com]
Sent: Monday, January 03, 2005 8:41 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Weird query question

Hey all,

I was modifying a query that a co-worker wrote so that it links with a
different sub query than the one it started with.  The query is a SQL string
executed with a openrecordset command.

The problem is that if I am getting the classic "Too few parameters..."
error message.  If I take the text and put it into a query and save it, I
can run the query fine.  When I try to run that query with the openrecordset
function using a querydef, I still get the error message.

Any ideas as to what is going on here?

Thanks,
Bobby

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com