Gary Kjos
garykjos at gmail.com
Wed Dec 20 11:00:51 CST 2006
Perhaps you need quotes around the sql string? GK On 12/20/06, Keith Williamson <Kwilliamson at rtkl.com> wrote: > Yeah. That is what is funny. That is how I got the Sql statement in > the first place. I had a bound form that worked, and simply copied the > recordsource. I used the same recordsource statement in my event for > the click button. > > And when I copy that same sql statement into a new query....it works > fine. > > ?? > > 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 11:33 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Unbound Subform Revisited > > 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 > > > -- > 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 > -- Gary Kjos garykjos at gmail.com