Joseph O'Connell
joconnell at indy.rr.com
Mon Jan 3 15:21:25 CST 2005
Bobby,
I have not been following this thread, so my apologies if this has already
been covered.
If the problem is using the syntax [Forms]![name of form]![name of control]
in a query, then why not replace it with a function that returns the value?
The query should then work correctly no matter how it is used.
Change the WHERE clause of the query from:
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")))
To this new WHERE clause:
WHERE ((([Payroll Line
Items].CostType)="WC")
AND ((Format([Start Date],"yyyymm"))
Between
ReturnFormValue("RptFrom")
And ReturnFormValue("RptTo"))
AND ((Format([DateAdded],"yyyymmdd"))
Between
ReturnFormValue("txtEntryFrom")
And
ReturnFormValue("txtEntryTo")))
Put this in a module:
Public Function ReturnFormValue (strFieldName as String)
Select Case strFieldName
Case "RptFrom"
ReturnFormValue =
Format([forms]![PerformanceChooser]![RptFrom],"yyyymm")
Case "RptTo"
ReturnFormValue =
Format([forms]![PerformanceChooser]![RptTo],"yyyymm")
Case "txtEntryFrom"
ReturnFormValue =
Format([forms]![PerformanceChooser]![txtEntryFrom],"yyyymm")
Case "txtEntryTo"
ReturnFormValue =
Format([forms]![PerformanceChooser]![txtEntryTo],"yyyymm")
Case Else
'put something here to catch any error in the calling argument
End Select
End Function
Joe O'Connell
-----Original Message-----
From: Bobby Heid <bheid at appdevgrp.com>
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Date: Monday, January 03, 2005 3:54 PM
Subject: RE: [AccessD] Weird query question
|That is what I am doing as I write this. LOL.
|
|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 3:25 PM
|To: accessd at databaseadvisors.com
|Subject: RE: [AccessD] Weird query question
|
|
|Hmmm, can you put it into the original query or not? It may be easier to
|pull the query as a table then, instead of as SQL.
|
|Drew
|
|-----Original Message-----
|From: Bobby Heid [mailto:bheid at appdevgrp.com]
|Sent: Monday, January 03, 2005 12:52 PM
|To: 'Access Developers discussion and problem solving'
|Subject: RE: [AccessD] Weird query question
|
|
|That is correct. The subquery name is ContractActualPayrollByDates.
|
|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 12:55 PM
|To: accessd at databaseadvisors.com
|Subject: RE: [AccessD] Weird query question
|
|
|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
|--
|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
|
|--
|AccessD mailing list
|AccessD at databaseadvisors.com
|http://databaseadvisors.com/mailman/listinfo/accessd
|Website: http://www.databaseadvisors.com