Beach Access Software
bchacc at san.rr.com
Wed Dec 20 10:32:59 CST 2006
Keith: Sometimes when I can't spot a problem in a lengthy SQL statement like this I paste it into the SQL view of a new query and then switch to the design view. Sometimes you can spot the problem that way. I would also paste the original record source SQL and the modified SQL into WordPad and eyeball them for differences. The line breaks should occur in roughly the same places so you can start by checking the length of each and the end of each line to find the differences. HTH Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Wednesday, December 20, 2006 8:14 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Unbound Subform Revisited Here is the sql statement: me.form.RecordSource = (SELECT [qryWeeklyTimesheetVarianceSummary].[Year], [qryWeeklyTimesheetVarianceSummary].[Month], [qryWeeklyTimesheetVarianceSummary].[Entity], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfRegPay]) AS [Reg Stand Cost], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvtPay]) AS [OT Standard Cost], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvt2Pay]) AS [OT2 Standard Cost], Sum([sumofregpay]+[sumofovtpay]+[sumofovt2pay]) AS [Total Standard Cost], Sum([qryWeeklyTimesheetVarianceSummary].[ActReg]) AS [Calc Reg Cost], Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt]) AS [Calc OT Cost], Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt2]) AS [Calc OT2 Cost], Sum([actreg]+[actovt]+[actovt2]) AS [Total Calc Cost], First([qryPayrollPaid].[SumOfAmountPaid]) AS FirstOfSumOfAmountPaid, First([qryPayrollDiffSummary].[SumOfHrs]) AS FirstOfSumOfHrs, First([qryPayrollDiffSummary].[SumOfWageDiff]) AS FirstOfSumOfWageDiff FROM qryPayrollDiffSummary RIGHT JOIN (qryPayrollPaid RIGHT JOIN qryWeeklyTimesheetVarianceSummary ON ([qryPayrollPaid].[Year]=[qryWeeklyTimesheetVarianceSummary].[Year]) AND ([qryPayrollPaid].[Month]=[qryWeeklyTimesheetVarianceSummary].[Month]) AND ([qryPayrollPaid].[Entity]=[qryWeeklyTimesheetVarianceSummary].[Entity]) ) ON ([qryPayrollDiffSummary].[Entity]=[qryWeeklyTimesheetVarianceSummary].[E ntity]) AND ([qryPayrollDiffSummary].[Month]=[qryWeeklyTimesheetVarianceSummary].[Mo nth]) AND ([qryPayrollDiffSummary].[Year]=[qryWeeklyTimesheetVarianceSummary].[Yea r]) WHERE [qryWeeklyTimesheetVarianceSummary].[Entity] = "00" And [qryWeeklyTimesheetVarianceSummary].[Month] = 10 And [qryWeeklyTimesheetVarianceSummary].[Year] = 2006 GROUP BY [qryWeeklyTimesheetVarianceSummary].[Year], [qryWeeklyTimesheetVarianceSummary].[Month], [qryWeeklyTimesheetVarianceSummary].[Entity];) Me.Form.Requery Anyone? Anyone? :) Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach Access Software Sent: Wednesday, December 20, 2006 10:37 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Unbound Subform Revisited Keith: Put a debug statement to print the SQL string right before the me.recordsource = and post it. Someone will see that there's probably a syntax/punctuation error. Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Wednesday, December 20, 2006 7:05 AM To: Access Developers discussion and problem solving Subject: [AccessD] Unbound Subform Revisited Hey Guys, If I want to change a bound form to an unbound form, am I going about it correctly if I do the following: 1) Add (3) unbound text fields for variables in form header; 2) Delete Record Source for form; 3) Add button to form header; 4) Add Event to button, to set form.recordsource = (original sql statement for record source, deleted in step 2, but also add where statements for the (3) variables in the unbound text fields) Shouldn't this run? I keep getting a syntax error on my sql statement, when I click the button. Even if I change the sql statement to exactly equal the original recordsource statement....I get the syntax error. ?? Thanks, Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com <http://www.rtkl.com/> -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date: 12/19/2006 1:17 PM -- 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 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date: 12/19/2006 1:17 PM