[AccessD] Weird query question

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





More information about the AccessD mailing list