[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